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

Cumulative count with multiple dates

Hello,

I would like to make a chart to track the status of my requests, by day.

Example with the following data:

IDCreatedApprovedClosed
101/07/201903/07/2019 
201/07/201905/07/201906/07/2019
302/07/201902/07/2019 
403/07/201907/07/201910/07/2019
503/07/2019  
603/07/200905/07/2019 
705/07/201908/07/201908/07/2019
806/07/201906/07/201909/07/2019
909/07/201910/07/2019 
1009/07/201909/07/2019 

 

As a table, I would like to obtain the following result:

 CreatedApprovedClosed
01/07/2019200
02/07/2019210
03/07/2019420
04/07/2019420
05/07/2019340
06/07/2019341
07/07/2019251
08/07/2019152
09/07/2019253
10/07/2019154

 

And as a chart :

Capture.PNG

 

Is this possible in Power BI ?

 

Thanks in advance,

Florent

2 REPLIES 2
dax
Community Support
Community Support

Hi florentbignier,

 

If you want to calculate cumulative value, you could follow below steps:

You could create a calendar table by 

calendar = CALENDAR(DATE(2019,7,1), DATE(2019,7,10))

Then create measure like below

apporve = CALCULATE(COUNT(stackchart[Approved]), FILTER(stackchart,stackchart[Approved]<=SELECTEDVALUE('calendar'[Date])))
closed = CALCULATE(COUNT(stackchart[Closed]), FILTER(stackchart,stackchart[Closed]<=SELECTEDVALUE('calendar'[Date])))
cretaed = CALCULATE(COUNT(stackchart[ID]), FILTER(stackchart,stackchart[Created]<=SELECTEDVALUE('calendar'[Date])))

Then you will get below result 

49.PNG50.png

But this seems not be same as your result. I am not clear about your first two columns' logic, so if possible, could you please explain for me in details? Then I will help you more correctly.

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thanks dax for your answer and your time 🙂

 

In fact, when a request has an approved date, I don't want it to count as created.

Example with the firts 3 days :

July 01 : Requests #1 and #2 were created. There are 2 requests created, 0 approved and 0 closed

July 02 : Request #3 was created, then it was approved. There are now 3 requests : 2 created (#1 and #2) and 1 approved (#3)

July 03 : Requests #4, #5 and #6 were created. Request #1 was approved. There are now 4 requests created (#2, #4, #5 and #6), 2 approved (#1 and #3) and 0 closed

 

That's it I don't know how to do 😞

 

Thanks in advance.

 

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.