Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
a_fman
Helper I
Helper I

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.

 

With this data I show prior 6 month trends overlaid with same 6 months period from the year before, so as it stands now August 2016 to Feb 2017, and August 2015 to Feb 2016. This helps with % variance year over year. Now two things here I need to figure out:

  1. How can I make it a dynamic date range to pull in only data from greater than or equal to 18 months ago
  2. How can I remove date range data from that middle 6 months that I don't need, in this instance from 7 months ago to 12 months ago inclusive.

My end data set should thus have August 2015 to February 2016 and August 2016 to current, and dynamically follow for -18months to -13months and -6months to current.

 

Thanks in advance.

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@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"



Lima - Peru

View solution in original post

4 REPLIES 4

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 !

 

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
Community Champion
Community Champion

@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"



Lima - Peru

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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.