Forum Discussion

marcp's avatar
marcp
Helper I
7 years ago
Solved

counting items with a date constraint

Hi all     I have a table of item, each item has a reference date it looks like this ItemRef, RefDate A, 18/02/2017 B, 20/02/2017 C, 21/02/2017   I need to count the number of item whose da...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    you first need to get the latest date for a given selection (covered by VAR) and then apply that filter in CALCULATE

    Measure = 
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
CALCULATE(DISTINCTCOUNT(Table1[ItemRef]), 'Calendar'[Date]<=CurrentDate)

    hope that helps :)

Stachu's avatar
Stachu
Community Champion
7 years ago

you first need to get the latest date for a given selection (covered by VAR) and then apply that filter in CALCULATE

Measure = 
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
CALCULATE(DISTINCTCOUNT(Table1[ItemRef]), 'Calendar'[Date]<=CurrentDate)

hope that helps :)

marcp's avatar
marcp
Helper I
7 years ago

Thank you Stachu

 

That's great !

 

Kind regards

Marc