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
cturner
Helper I
Helper I

Dynamically compute last X days trend from selected date

I've done a bit of digging, and the approaches I've already seen here are failing because there is date grain to the output and the row context for the output determines the min/max/lastdate/selectedvalue of any measure. 

I don't need a total for the period.   I need the daily trend for X days preceding the single selected date.  I'm missing something basic here and I can't seem to figure it out.

I've got a date dim and a performance fact.  They are related on date, obviously.  I am using one of the calendar visuals to allow the users to select a single date.  When they do this I want all my trendlines to update to a period of XX days before that selection.

I've been trying something like this:

last 90 perf = calculate(sum('fact'[perf]),filter('Dim date','Dim date'[reportdate]<=selectedvalue('Dim date'[reportdate],lastdate('fact'[date])) && 'Dim date'[reportdate]>=selectedvalue('Dim date'[reportdate],lastdate('fact'[date])-90)))


But this doesn't work.  Any help?

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

Hi@ cturner

After my research, you can do it follow my steps like below:

 

Step 1:

Add a date table and create the relationship between fact table ,and don’t create relationship between fact table and Dim date table

1.png

 

Step 2:

Add this measure:

last 90 perf = 
CALCULATE (
SUMX('fact', 'fact'[Qty]),FILTER('Date', 'Date'[Date]>=EDATE(min('Dim date'[Date]), -3)&&'Date'[Date]<=SELECTEDVALUE('Dim date'[Date])))

Drag ‘Dim date’ [Date] into slicer

 

Result:

2.png

Here is Demo, please try it.

https://www.dropbox.com/s/v4uaanf1152k0f0/Dynamically%20compute%20last%20X%20days%20trend%20from%20s...

 

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

Hi@ cturner

After my research, you can do it follow my steps like below:

 

Step 1:

Add a date table and create the relationship between fact table ,and don’t create relationship between fact table and Dim date table

1.png

 

Step 2:

Add this measure:

last 90 perf = 
CALCULATE (
SUMX('fact', 'fact'[Qty]),FILTER('Date', 'Date'[Date]>=EDATE(min('Dim date'[Date]), -3)&&'Date'[Date]<=SELECTEDVALUE('Dim date'[Date])))

Drag ‘Dim date’ [Date] into slicer

 

Result:

2.png

Here is Demo, please try it.

https://www.dropbox.com/s/v4uaanf1152k0f0/Dynamically%20compute%20last%20X%20days%20trend%20from%20s...

 

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'd found the same/similar approach shortly after posting.  It does work.  It gets a little fiddly when you want to do time intelligence (mtd,ytd,yoy,etc) on top of it, but I eventually made it work for this requirement. 

It also has unexpected impacts to the interactions between visuals in the report as filtering measure data by the selected date means the rest of the date grain dimensionality in the report isn't as useful.  There's probably additional logic to be implemented that would resolve this.

Thanks.

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.