Dynamically load last three months of data in dataflow

[karthik77700]

I have a requirement to load dynamically last three months based on max data available in Power Bi Dataflow

For example,This is April month but i have max snapshot date as Feb 2029 so i need to load from Dec,Jan and Feb.

If March data is loaded dynamically it will load Jan,feb and Mar .

[ibarrau]

Hi, if you can query the source with SQL go with karthik suggestion. Otherwise you can get the date and filter your data with the following power query

Date.AddMonths( [DateFromToSubstractDays] ,-3)

In case you want to get las 3 months from today, you can use "DateTime.FixedLocalNow()" for the date.

Regards,

[karthik77700]

I have created sample data

where and how  to apply this formula in M query editor.

My query editor looks like this now,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg1W0lEyMjAyMTAyslSK1YlW8nSBiRgaG4JFgt0hIsaGRlARD2+YiKGxgVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COuntry = _t, Snapshotdate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COuntry", type text}, {"Snapshotdate", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Snapshotdate", type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{"Snapshotdate", type date})
in
    #"Changed Type2"

[ibarrau · Accepted solution]

Alright. Following the example let me show you how to do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg1W0lEyMjAyMTAyslSK1YlW8nSBiRgaG4JFgt0hIsaGRlARD2+YiKGxgVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COuntry = _t, Snapshotdate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COuntry", type text}, {"Snapshotdate", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Snapshotdate", type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{"Snapshotdate", type date}),
    // New Code down here
    var_last3months = Date.AddMonths(List.Max(#"Changed Type2"[Snapshotdate]), -3),
    var_last3calendarmonths = Date.AddMonths(Date.StartOfMonth(List.Max(#"Changed Type2"[Snapshotdate])),-2),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each [Snapshotdate] >= var_last3calendarmonths)
    // New Code finishes here
in
    #"Filtered Rows"

Take a look at the last 3 lines.

I have created two variables that you can choose depending on what you mean with "last 3 months".

• var_last3months = If you want it to substract 90 days, for example consideran febrary 29th as the last day, then 3 months would be november 29th. Use this variable at the "Filtered Rows" step
• var_last3monthscalendar = If you want to keep completed months from 1 to last one since current month. Use this one, it will take from December 1st to Febrary 29th.

The Step "Filtered Rows" is the one finally filtering by last 3 months with the variable. You can filter Snapshotdate by the one you have chosen.

I hope that make sense.

[karthik77700]

Thanks for your help .It worked out .

[Anonymous]

Create a Table/Query (SELECT Date FROM datatable where Date <= (somedate) with all dates needed for Dataflow and Merge the query with this query for Dataflow