Dynamically compute last X days trend from selected date

[cturner]

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?

[v-lili6-msft · Accepted solution]

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

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:

Here is Demo, please try it.

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

Best Regards,

Lin

[cturner]

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.