Forum Discussion
Dynamic Last 7 day
I am trying to calculate the sales for the last 7 days, but the last 7 days will be dynamic, based on the date selected in a slicer.
eg. if the date selected in the slicer is 2017/07/18, then the sales should be calculated for 7 days prior to 2017/07/18. ie. from 2017/07/11 to 2017/07/18.
Following is the measure I created:
7day_Sales = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]< [selected_Date] &&'Table'[Date]>= [selected_Date]-7))
The measure selected_Date stores the date selected in the slicer. Following is the formula:
selected_Date = IF(HASONEVALUE('Table'[Date]),VALUES('Table'[Date]),TODAY())
The measure 7day_Sales works fine when TODAY() is used instead of selected_Date or even if a hardcoded date is passed.
How can this be implemented?
14 Replies
vanessafvg hardcoding the value works. Yes the date format is correct.
the formula works fine when I hardcode the value as below:
7day_Sales =
VAR selected_Date = IF(HASONEVALUE('Table'[Date]),2017/07/18),TODAY())
RETURN
CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]< [selected_Date] &&'Table'[Date]>= [selected_Date]-7))
But when I try using VALUES('Table'[Date]) instead hardcoding the date, the measure returns data for all the dates in the table.
Is this because the left and right side of the filter condition are hitting the same table?
HI vanessa,
Power bi not support dynamic calculated columns based on slicer, you can create dynamic measure based on slicer.
In addition, you can use query parameter to create a dynamic calculated column on power query.
Deep Dive into Query Parameters and Power BI Templates
Regards,
Xiaoxin Sheng
