Forum Discussion

karthik77700's avatar
karthik77700
Icon for Helper I rankHelper I
2 years ago
Solved

Dynamically load last three months of data in dataflow

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 .

 

 

Need Help
  • ibarrau's avatar
    ibarrau
    2 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • ibarrau's avatar
    ibarrau
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      karthik77700
      Icon for Helper I rankHelper I
      2 years ago

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

        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.