Forum Discussion

H_insight's avatar
H_insight
Helper V
4 years ago
Solved

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 ...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    4 years ago

    H_insight 

    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)
    Return
    CALCULATE (
    COUNT(Employee[SaleValue]),
    FILTER (
    ALL ('Calendar' ),
    Calendar[Date] > _Lastdate - SELECTEDVALUE('Last N Days'[Last N Days])
     
    )
    )
AllisonKennedy's avatar
AllisonKennedy
Community Champion
4 years ago

H_insight 

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)
Return
CALCULATE (
COUNT(Employee[SaleValue]),
FILTER (
ALL ('Calendar' ),
Calendar[Date] > _Lastdate - SELECTEDVALUE('Last N Days'[Last N Days])
 
)
)
H_insight's avatar
H_insight
Helper V
4 years ago

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.