counting items with a date constraint

[marcp]

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 date is less or equal to the date in the calendar table (made with calendarauto)

for example

CalendarDate, ItemCount

17/02/2017, 0

18/02/2017, 1 (just A)

19/02/2017, 1 (just A)

20/02/2017, 2 (A and B)

21/02/2017, 3 (A, B and C)

It seems easy but I can't figure out

Could you help me ?

Thanks

Regards

Marc

[Stachu · Accepted solution]

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]

Thank you Stachu

That's great !

Kind regards

Marc