Forum Discussion
Last N Days
Hi All,
I am creating a measure to return the count of all (non-blank) values based on a slicer. The slicer is a series 0,90. For some reason, I am not getting any results, so I wonder if anyone can shed the light on what I am missing?
Basically what I want the table to show is based on the selected N days of the slicer, to return how many non-blank rows have data/values.
Here is code:
Last N days =
VAR _Lastdate = TODAY()
Return
CALCULATE (
COUNT(Employee[SaleValue]),
FILTER (
ALL ('Calendar' ),
Calendar[Date] > _Lastdate - SELECTEDVALUE('Last N Days'[Last N Days]) &&
Calendar[Date] < ( Calendar[Date] )
)
)
Based on the sample data if today is 24/2/2020, the last 30 days would be go back until 25/1/2020, so I would expected 8 records.
ps. I am not after relative dates.
Many thanks
A couple of things:
1) your data doesn't have any records in the last 90 days. Most recent is May 2020.
2) You're filtering for date < date which will make your measure always blank.
If we change TODAY() in your measure to May 2020 and remove that filter, you'll get results:
Last N days =VAR _Lastdate = DATE(2020, 05,30)ReturnCALCULATE (COUNT(Employee[SaleValue]),FILTER (ALL ('Calendar' ),Calendar[Date] > _Lastdate - SELECTEDVALUE('Last N Days'[Last N Days])))
2 Replies
A couple of things:
1) your data doesn't have any records in the last 90 days. Most recent is May 2020.
2) You're filtering for date < date which will make your measure always blank.
If we change TODAY() in your measure to May 2020 and remove that filter, you'll get results:
Last N days =VAR _Lastdate = DATE(2020, 05,30)ReturnCALCULATE (COUNT(Employee[SaleValue]),FILTER (ALL ('Calendar' ),Calendar[Date] > _Lastdate - SELECTEDVALUE('Last N Days'[Last N Days])))AllisonKennedy Thanks for your reply.
The updated measure has a hardcoded date of 30/05/2022 and the aim is to have a dynamic measure.
You have sparked my thinking (Thank you!), and I think I got the answer now. I have replaced _lastdate with Today() and most importantly I have added <= to the 2nd part of the filter.
VAR _Lastdate = TODAY() Return CALCULATE ( COUNT(Employee[SaleValue]), FILTER ( ALL ('Calendar' ), Calendar[Date] > _Lastdate - SELECTEDVALUE('Last N Days'[Last N Days]) && Calendar[Date] <= _Lastdate ) )I will mark this as solution and thanks again for sharing your feedback.
