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 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

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 :)

2 Replies

  • 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