Forum Discussion

fabiomg777's avatar
fabiomg777
Frequent Visitor
8 years ago
Solved

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 s...
  • danextian's avatar
    danextian
    8 years ago

    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
danextian's avatar
danextian
Icon for Super User rankSuper User
8 years ago

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's avatar
    fabiomg777
    Frequent Visitor
    8 years ago

    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's avatar
      danextian
      Icon for Super User rankSuper User
      8 years ago

      I missed this part ().

       

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

      • fabiomg777's avatar
        fabiomg777
        Frequent Visitor
        8 years ago

        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