cancel
Showing results 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.

Helper I

## 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 Date ID Completed Out Date 11.05.2018 A1 yes 11.06.2018 18.05.2018 B2 no 20.05.2018 C3 no 06.06.2018 L13 no 17.06.2018 D4 no 18.06.2018 E5 yes 20.06.2018 18.06.2018 F6 no 19.06.2018 G8 yes 19.06.2018 24.06.2018 H9 yes 26.06.2018 25.06.2018 I10 yes 26.06.2018 25.06.2018 J11 no 26.06.2018 K12 no M14 yes 03.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
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

Helper I

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

Solution Sage

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.

Announcements

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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors