none
Filter date up to last full month RRS feed

  • Question

  • Dear community,

    I was wondering how to filter my power query such that it only includes the last full month, or the last date of the pervious month?

    For example, today is 5/3/2016, I'm trying to figure out how to filter the data up to 4/30/2016

    Thank you,


    Kai

    Wednesday, May 4, 2016 12:53 AM

Answers

  • A list of dates? Like this?

    let
    
        Today = 
            DateTime.Date(DateTime.LocalNow()),
    
        StartDate = 
            #date(Date.Year(Today),1,1),
    
        EOLastMonth = 
            Date.EndOfMonth(Date.AddMonths(Today,-1)),
    
        DatesTable = 
            Table.FromList(
                List.Dates(StartDate, Number.From(EOLastMonth) - Number.From(StartDate) +1, #duration(1,0,0,0)), 
            Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error)
    
    in
        DatesTable

    Friday, May 6, 2016 5:31 AM

All replies

  • Here's some sample code that creates a list of dates from January 1st of last year through today, then filters the table to show only last month, and only the last day of the last month:

    let
    
        Today = 
            DateTime.Date(DateTime.LocalNow()),
    
        StartDate = 
            #date(Date.Year(Today)-1,1,1),
    
        EOLastMonth = 
            Date.EndOfMonth(Date.AddMonths(Today,-1)),
    
        DatesTable = 
            Table.FromList(
                List.Dates(StartDate, Number.From(Today) - Number.From(StartDate), #duration(1,0,0,0)), 
            Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    
        FilterByLastMonth = 
            Table.SelectRows(DatesTable, each Date.IsInPreviousMonth([Date])),
    
        FilterByEndOfLastMonth = 
            Table.SelectRows(DatesTable, each ([Date] = EOLastMonth))
    
    in
        FilterByEndOfLastMonth

    Wednesday, May 4, 2016 7:28 AM
  • To get the current date, use: DateTime.FixedLocalNow()

    To remove the hh:mm:ss part: DateTime.Date()

    To get the start of the current month: Date.StartOfMonth()

    to get the last day of the last full month, substract one day: - #duration(1,0,0,0)

    let
        Source = DateTime.FixedLocalNow(),
        #"Extracted Date" = DateTime.Date(Source),
        Startofmonth = Date.StartOfMonth( #"Extracted Date"),
        Lastdayoflastmonth= Startofmonth - #duration(1,0,0,0)
    in
        Lastdayoflastmonth

    Wednesday, May 4, 2016 7:28 AM
  • Thanks RollingHippy,

    Is there a between function to get from say 1/1/2016 to EndOfLastMonth (e.g. 4/30/2016)?

    Wednesday, May 4, 2016 4:22 PM
  • A list of dates? Like this?

    let
    
        Today = 
            DateTime.Date(DateTime.LocalNow()),
    
        StartDate = 
            #date(Date.Year(Today),1,1),
    
        EOLastMonth = 
            Date.EndOfMonth(Date.AddMonths(Today,-1)),
    
        DatesTable = 
            Table.FromList(
                List.Dates(StartDate, Number.From(EOLastMonth) - Number.From(StartDate) +1, #duration(1,0,0,0)), 
            Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error)
    
    in
        DatesTable

    Friday, May 6, 2016 5:31 AM