Power Query to get data from the last 6 months

[fabiomg777]

Hi friends, how are you guys? I'm very new to all this DAX, Power BI, Power Query etc.

I just start using Power Query and Power Pivot before I go to Power BI.

I need help because I cant find a solution to this. I am trying to get a dynamic data for the last 6 months and trying to make Power Query to get it but I just dont know how to. Tried a couple of code I found here but none worked.

Code is (in Portuguese, sorry):

let



    Fonte = Access.Database(File.Contents("C:\Users\DB.accdb"), [CreateNavigationProperties=true]),
    _BASE_LEVES = Fonte{[Schema="",Item="BASE_LEVES"]}[Data],
    #"Outras Colunas Removidas" = Table.SelectColumns(_BASE_LEVES,{"NOME", "CTT", "PAGTO", "DUTIL"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Outras Colunas Removidas", each [PAGTO] > #datetime(2017, 9, 22, 0, 0, 0))
in
    #"Linhas Filtradas"

So I guess I need to change that 

each [PAGTO] > #datetime(2017, 9, 22, 0, 0, 0)

(PAGTO is the date table)

Thanks for reading, 

[danextian]

Hi @fabiomg777,

Is the 6 months based on the current date?

My approach in this case would be to dynamically generate my start date which i can use to filter my data.  The script would be something like this (I don't have Power BI right now so I might have missed something causing a formula error):

=List.Min(List.FirstN( List.Distinct( List.Sort( List.Transform( List.Dates(DateTime.Date(DateTime.LocalNow), 10, #duration(28,0,0,0)), Date.StartOfMonth ), Order.Descending ) ), 6 ) )

I would name this query StartDate and would use it the way i would a date parameter. In your case, your row filter script would now change to:

each [PAGTO] >= StartDate 

Or if you need it in  datetime type, you may use:

each [PAGTO] >= DateTime.From(StartDate)

[fabiomg777]

Hi Danextian, thank you very much. I really appreciate that.

I put the code that way:

let

StartDate = List.Min(List.FirstN( List.Distinct( List.Sort( List.Transform( List.Dates(DateTime.Date(DateTime.LocalNow), 10, #duration(28,0,0,0)), Date.StartOfMonth ), Order.Descending ) ), 6 ) ),

    Fonte = Access.Database(File.Contents("db.accdb"), [CreateNavigationProperties=true]),
    _BASE_LEVES = Fonte{[Schema="",Item="BASE_LEVES"]}[Data],
    #"Outras Colunas Removidas" = Table.SelectColumns(_BASE_LEVES,{"NOME", "CTT", "PAGTO", "DUTIL"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Outras Colunas Removidas", each [PAGTO] >= DateTime.From(StartDate) )
in
    #"Linhas Filtradas"

But then I got the error 

Expression.Error: The DateTime.Time function expects an input of type DateTime or DateTimeZone.
Details:

Function

PAGTO seems to be Date/time

Thanks again for your help

[danextian]

I missed this part ().

Instead of DateTime.LocalNow, it should have been DateTime.LocalNow() which is the Excel's Now() equivalent in M. 

[fabiomg777]

Thanks Dane!

I got no errors now, but no data is loading:

I went to explore a little bit and clicked on view native query, it is showing the following:

select [_].[NOME],
    [_].[CTT],
    [_].[PAGTO],
    [_].[DUTIL]
from 
(
    select [NOME],
        [CTT],
        [PAGTO],
        [DUTIL]
    from [BASE_LEVES] as [$Table]
) as [_]
where [_].[PAGTO] >= #2018-06-01 00:00:00#

I kinda know what is going on but dont know how to fix it xD
But I am happy that  I am learning

Thanks again

[danextian · Accepted solution]

Here's the query if you want to get the actual date six months ago from today:

let
    //returns the current time
    Source = DateTime.LocalNow(),

    //extracts the date component of the current time
    CurrentDate = DateTime.Date( Source ),

    /*generates a list of dates starting at current date of 10 rows
     with an increment of negative 28 days, returns earlier dates
     starting current date sinc interval is negative
     #duration(d, h, m, s) */ 
    GenerateDates = List.Dates( CurrentDate, 10, - #duration( 28, 0, 0, 0 ) ),

    //extracts start of month from the list of dates
    GetMonthStart = List.Transform( GenerateDates, Date.StartOfMonth ),

    //removes duplicate date values
    DistinctDates = List.Distinct( GetMonthStart ),

    //returns just the first 6 rows
    Last6Months = List.FirstN( DistinctDates, 6 ),

    //returns the earliest month from the list
    EearliestMonthDay = List.Min( Last6Months ),

    //returns the day of the current month
    CurrentMonthDay = Date.Day( CurrentDate ),

    //returns the month number 6 months ago..1 for jan, 2 for feb... 
    MonthNumber6MonthsAgo = Date.Month( EearliestMonthDay ),
    
    //returns the yer 6 months ago
    Year6MonthsAgo = Date.Year( EearliestMonthDay  ),
    
    //returns the end of month day 6 months ago
    EndOfMonthDay6MonthsAgo = Date.Day( Date.EndOfMonth( EearliestMonthDay ) ),

    /*finally, returns the date 6 months ago
        if your requirement is to start the filter one day after the the date 6 months ago
        just add #duration(1, 0, 0, 0 ) after the closing parenthesis of this variable
        or you just can just > instead of >= in your date filter */
    //also checks if the current month day is greater than the 6 months ago day and returns whichever is lesser

    Date6MonthsAgo = #date(Year6MonthsAgo , MonthNumber6MonthsAgo, 
                    if CurrentMonthDay > EndOfMonthDay6MonthsAgo then EndOfMonthDay6MonthsAgo  
                    else CurrentMonthDay  )  
in
    Date6MonthsAgo

[fabiomg777]

Nice! It worked very well!

I really feel like myself trying to learn VLOOKUP back in 2008 with Power Query, I will study it deeply this year!

You kept me motivated, thanks!

(I will probably post more because it was just the first part of what I am doing xd, I made what I needed in VBA, really long code to write and the possibility of doing it very quickly via Power Query is cool)

Thanks again,

bye!

[danextian]

I realized while browsing the MSDN site for M that there is this Date.AddMonths function which is a more elegant wait of adding to or subtracting a number of months from a particular date.

[danextian]

Thanks for the feedback. I've realized though that there is a minor error in my query. It returns the date 5 months ago as feb is one month ago from mar. The second parameter in FirstN should have been 7.

[danextian]

Hi @fabiomg777,

To easily understand, I split the script into several steps instead of nesting them all in one. Please enter this script in a separate query and name StartDate. This will return the start of month 6 months ago starting today. Since we are in March, the script will return Oct 1, 2017.

let
    //returns the current time
    Source = DateTime.LocalNow(),

    //extracts the date component of the current time
    CurrentDate = DateTime.Date( Source ),

    /*generates a list of dates starting at current date of 10 rows
     with an increment of negative 28 days, returns earlier dates
     starting current date sinc interval is negative
     #duration(d, h, m, s) */ 
    GenerateDates = List.Dates( CurrentDate, 10, - #duration( 28, 0, 0, 0 ) ),

    //extracts start of month from the list of dates
    GetMonthStart = List.Transform( GenerateDates, Date.StartOfMonth ),

    //removes duplicate date values
    DistinctDates = List.Distinct( GetMonthStart ),

    //returns just the first 6 rows
    Last6Months = List.FirstN( DistinctDates, 6 ),

    //returns the earliest month from the list
    EearliestMonth = List.Min( Last6Months )
in
    EearliestMonth

If you need to get the actual date 6 months ago, please let me know. 

[danextian]

I'll go check later when i have my power bi desktop. That query should be returning a past date, not a future one.