Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ohojames
Frequent Visitor

How to calculate DISTINCTCOUNT() on cumulative number

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

 

2561-07-24 08_43_51-Microsoft Excel - สมุดงาน1.png

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

 

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

View solution in original post

2 REPLIES 2
v-huizhn-msft
Employee
Employee

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.