Forum Discussion
Helper IDynamically compute last X days trend from selected date
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.
Best Regards,
Lin
Community SupportHi@ 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.
Best Regards,
Lin
Helper II'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.