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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply

Cumulative Count by Date help

Hi,
I think I'm very close but I can't figure out the last piece of the puzzle. The solution here might be close but I need : https://community.powerbi.com/t5/Desktop/Cumulative-Count-by-Date/m-p/368918#M167100

 

Here's a sample of my data:

In DateIDCompletedOut Date
11.05.2018A1yes11.06.2018
18.05.2018B2no 
20.05.2018C3no 
06.06.2018L13no 
17.06.2018D4no 
18.06.2018E5yes20.06.2018
18.06.2018F6no 
19.06.2018G8yes19.06.2018
24.06.2018H9yes26.06.2018
25.06.2018I10yes26.06.2018
25.06.2018J11no 
26.06.2018K12no 
 M14yes03.06.2018

 

What I need to do is:
* Counts of [Complete] = "no" on a day/monthly basis (preferably weekly but day/month was more priority)

 

This would mean, today the 27th June, a line chart should show a count of 7 no's. I want to show a line chart of outstanding incompleted IDs going back 4-5 months.

 

Any assistance appreciated!

3 REPLIES 3
Seward12533
Solution Sage
Solution Sage

Unless I'm missing something I think all you need is to do define a measure for the count of incomplete

 

Count InComplete = CALCULATE(COUNTROWS('Count Complete'),'Count Complete'[Completed]="No")

 

And then write a measure for Cumulative count of that measure similar to the one you posted.  Assuming you have a date table linked you can then plot this over any period you want Day,Week, Month, Year, Quarter, etc...

 

 

You may also want to look at The TOTALMTD function

results1.PNG

 

Hi @Seward12533,

Unfortunately no success. When graphed, for example, expectation would be that on June 17th, the value of the red line would be at 4. 

 

I'll look into TOTALMTD. Thanks

You need to build a mesure to calcate cumulative Count of Incomplete either by using the built in TOTALMTD, TOTALYTD or building your own similar to the post you linked to in your original question. Someting like

 

Cumulative Count of Inc = IF(MIN(Calendar[Calendar_Date])<=NOW(),CALCULATE([Count Inc],FILTER(ALL(Calendar),Calendar[Calendar_Date]<=MAX(Calendar[Calendar_Date]))))

 

The IF blocks this from calculating for dates in the future assuming your date table does as well.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors