Forum Discussion

a_fman's avatar
a_fman
Icon for Helper I rankHelper I
9 years ago
Solved

Dataset DAX Query Size Reduction - Swap 18 months for 6 months back, plus 6 months of prior year?

I currently have a dataset that is non-dynamic in terms of what gets pulled in. Every months I change the earliest data date forward by a month so that I always have the most recent 18 months of data...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    a_fman

     

    you can apply a date filtering in Query Editor

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDRDcQgDAPQXfiuVBJCKLNU3X+NK5xsnXW/T8aKue9iZ7XTq/VyFLvKc2xykE9QA0UFxX+qs2uAEtSYGkyRLnYlaJJwl1UegXrj9e4gXm+Brp3Kl3qVjYt8yEZNBSh0o6aSqS4bF7UmG5UmH6Zs3GSycZ9qslG7dmr8/sR346L2/sTzAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNMonths([Date], 18) or Date.IsInCurrentMonth([Date])),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-12) or Date.IsInPreviousNMonths([Date], 6) or Date.IsInCurrentMonth([Date])) 
in
    #"Filtered Rows1"
Datatouille's avatar
Datatouille
Icon for Solution Sage rankSolution Sage
9 years ago

Hi a_fman

 

1. Do you need a proper separate calculated table updated at refresh ?

2. Or do you need to use this table as a Table argument in Measures ?

In any cases, you need to have a proper Calendar or Date Table in your Data model.

 

For 1. you can use either power query or DAX to calculate a table (Excel 2016, Power BI Desktop only for DAX).

I give you my suggestion using DAX. Supposing you want to go 6 months backward from today at each refresh, this will give you a new table ('Modeling' > New Table):

CustomTable=

VAR Last6months = DATESINPERIOD( 'Calendar'[Date] , TODAY() ,-6 , MONTH )
VAR Last6monthsLY = SAMEPERIODLASTYEAR( Last6months ) RETURN
UNION( Last6monthsLY , Last6months )

 

will give you the full table you need.

 

If you need 2., please give us more information !

 

  • a_fman's avatar
    a_fman
    Icon for Helper I rankHelper I
    9 years ago

    Ok this is a great start. I currently stage all of my data in staging tables. On those staging tables is where I apply my date filtering to limit my dataset (PowerBI file is currently around 600MB, which is why I'm trying to pull out unneccesary data). I do have a DateDim present for my filtering, and for my multitude of dashboards I use logic I built for DaysInPast=x or MonthsInPast between y and z, however I do not have relationships to my staging tables from that and would prefer to keep it that way.

     

    Is there a way to do this without unioning and to just have a dax expression on the staging tables?

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      9 years ago

      a_fman

       

      you can apply a date filtering in Query Editor

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDRDcQgDAPQXfiuVBJCKLNU3X+NK5xsnXW/T8aKue9iZ7XTq/VyFLvKc2xykE9QA0UFxX+qs2uAEtSYGkyRLnYlaJJwl1UegXrj9e4gXm+Brp3Kl3qVjYt8yEZNBSh0o6aSqS4bF7UmG5UmH6Zs3GSycZ9qslG7dmr8/sR346L2/sTzAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNMonths([Date], 18) or Date.IsInCurrentMonth([Date])),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-12) or Date.IsInPreviousNMonths([Date], 6) or Date.IsInCurrentMonth([Date])) 
in
    #"Filtered Rows1"
      • a_fman's avatar
        a_fman
        Icon for Helper I rankHelper I
        9 years ago

        Made a couple minor tweaks but this worked perfectly, thanks!

         

        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc/LDcMwEAPRXnQ2YO3qX4vh/ttIlgqQ0fWBB87zJLvt9mw1XamM9F5PckiXFEiTVEiVNEiRdIhLBsQkE5Il6y++JJZBcxNe+35tuO379m/UQhrTJJVpksI0iTNNYkyTZKaF2GKaZDJNMo40UT/SRO1IE1Wm9ZDCNIkzTWJMk2SmhSyWBUyGBQx2BXRmBbSjKqQeUSHlaApxJo04x6KA79n3Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", let latest = List.Max(#"Changed Type"[Date]) in each Date.IsInPreviousNMonths([Date], 18) or [Date] = latest),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-12) or Date.IsInPreviousNMonths([Date], 6) or Date.IsInCurrentMonth([Date]))
        in
        #"Filtered Rows1"