cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
a_fman Regular Visitor
Regular Visitor

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

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

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

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Highlighted
Datatouille Established Member
Established Member

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

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 Regular Visitor
Regular Visitor

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

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 Super Contributor
Super Contributor

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

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




a_fman Regular Visitor
Regular Visitor

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

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
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 306 members 3,582 guests
Please welcome our newest community members: