Solved: Power Query to get data from the last 6 months - Page 2

fabiomg777 · ‎03-22-2018

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 · ‎03-22-2018

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

Power Query to get data from the last 6 months

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly