How to calculate DISTINCTCOUNT() on cumulative number

[ohojames]

Hi folks,

I'd like to calculate how many stores that sells 0 item in between start date and the latest day base on a table name "store" and its data structure below

Expected Result : 2 stores (C and D)

My attempted calculation is

noVolumnStore = 
VAR startDate = DATE(2018,07,01)
VAR latestDate = MAX( store[Datekey] )
VAR changeVolumn = CALCULATE( SUM(Cumulative_sold_item), store[Datekey] = latestDate ) - CALCULATE( SUM(Cumulative_sold_item), store[Datekey] = startDate)

RETURN
CALCULATE(  DISTINCTCOUNT(  store[Store]), changeVolumn = 0)

Thank you in advance

[v-huizhn-msft]

Hi @ohojames,

Have you resolved your issue according to the solution @Anonymous posted? If it is, please mark the reply as answer, and more people will benefit from here.

Best Regards,
Angelia

[Anonymous · Accepted solution]

CALCULATE( SUM(Cumulative_sold_item), store[Datekey] = latestDate )

In the above snippet, you need to compare the store as well. so you may need to add this conditon as well.

Store[StoreID] = Earlier ( Store[StoreID])

So it would be ...

CALCULATE( SUM(Cumulative_sold_item), store[Datekey] = latestDate,Store[StoreID] = Earlier ( Store[StoreID]) )

Thanks

Raj