cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Power Query to get data from the last 6 months

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, 

1 ACCEPTED SOLUTION

Accepted Solutions

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

10 REPLIES 10
Super User II
Super User II

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)

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

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

image.png

 

Thanks again for your help

I missed this part ().

 

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Thanks Dane!

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

 

image.png

 

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

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. 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

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!

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors