Forum Discussion

powerbiuser101's avatar
powerbiuser101
Advocate I
8 years ago

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!

Need Help

3 Replies

  • Seward12533's avatar
    Seward12533
    Solution Sage
    8 years ago

    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

    • powerbiuser101's avatar
      powerbiuser101
      Advocate I
      8 years ago

       

      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

      • Seward12533's avatar
        Seward12533
        Solution Sage
        8 years ago

        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.