Forum Discussion
Filtering for data 2 weeks prior the current date
I am trying to figure out how to filter a date field for 2 weeks prior the current date. The dashboard I am creating has to be completely automated, so using filter where I am selecting a spefic date...
Hi Anonymous ,
First you need two separate tables, a date table and a fact table. They are not related to each other.
Then create a measure, like the following.
Measure = VAR _selected = MAX ( Slicer[Date] ) VAR _start = _selected - 14 RETURN IF ( SELECTEDVALUE ( 'Table'[Date] ) >= _start && SELECTEDVALUE ( 'Table'[Date] ) <= _selected, 1 )Then put the measure in filter pane and set it show items which is 1.
The output:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vapid128
Solution Specialist
Solution Specialist14_days_ago =
VAR __LAST_DATE = LASTDATE(DateTable[Date])
VAR __FIRST_DATE = FIRSTDATE(DateTable[Date])
return
AVERAGEX(
DATESBETWEEN(
DateTable[Date],
DATEADD(__FIRST_DATE, -14, DAY),
DATEADD(__LAST_DATE, -14, DAY)
),
CALCULATE([WHATEVER YOU WANT TO CALCULATE])
)