Forum Discussion
Dynamic date range/line chart...
I've created a dynamic measure of # of days. When you select the number of days you want, the report compares the current value to the value in the past (the # of days in the past). I have no problem with this. However, I'm trying to add a line chart to the report that also updates based on the days selection. So if someone selected 30 days in the past, then the date range in the line chart should update to only show 30 days (max date - 30 days). I've seen this done before and I've written the formula the exact same way I saw it, but for some reason the line chart (or table) is not updating properly. All the dates in the table still show. Here's how I'm writing the measure (I'm not including the dynamic aspect here for the sake of simplicity):
[Trend Value] = CALCULATE( [Total Value], FILTER( Dates, Dates[Date] >= MAX(Dates[Date]) - 30))
If you created a table with the Date field in it and then added this Trend Value measure to it, shouldn't the table adjust to only show the last 30 days? You can see some sample data here.
Thank you.
Hi iDataDrew,
You can try to use below formula to get last month running total.
LM Total= var currentDate=MAX('Table'[Date]) return CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[Date]>=DATE(YEAR(currentDate),MONTH(currentDate)-1,DAY(currentDate))&&[Date]<=currentDate))Please ensure your formula not contain any specific date filter which can broke the current filter.(all allexcept)
Regards,
Xiaoxin Sheng
1 Reply
Hi iDataDrew,
You can try to use below formula to get last month running total.
LM Total= var currentDate=MAX('Table'[Date]) return CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[Date]>=DATE(YEAR(currentDate),MONTH(currentDate)-1,DAY(currentDate))&&[Date]<=currentDate))Please ensure your formula not contain any specific date filter which can broke the current filter.(all allexcept)
Regards,
Xiaoxin Sheng