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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ExcelMonke
Super User
Super User

Cumulative count for missing data

Hello, 
I am trying to figure out how to calculate a cumulative count where, if a category returns 0, it will take the "previous" value. I have tried a couple methods but it doesn't seem to work. This must be a measure due to the size of the data set. 

This is my current DAX:

 

Cumulative Tester =
VAR _Count =
    CALCULATE (
        DISTINCTCOUNT ( Data[OrderId] ),
        FILTER (
            ALLSELECTED ( Data ),
            Data[DaystoRespond] <= MAX ( Data[DaystoRespond] )
        )
    ) + 0
VAR _Total =
    CALCULATE ( DISTINCTCOUNT (  Data[OrderId] ), ALLSELECTED ( Data) )
VAR _AccountForZero =
    CALCULATE (
        DISTINCTCOUNT (  Data[OrderId] ),
        FILTER (
            ALLSELECTED ( Data ),
            ( Data[DaystoRespond] - 1 )
                <= MAX ( Data[DaystoRespond] ) - 1
        )
    ) + 0
VAR _NoBlankCount =
    IF ( _Count = 0, _AccountForZero, _Count )
RETURN
    // DIVIDE(_NoBlankCount,_Total)
    _AccountForZero

 


This gives me the following result:

ExcelMonke_0-1722982787959.png

 

The intended result should be that, on row 26, the result should be 12948, instead of 0. 

Would anyone be able to help?

Thanks! 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





2 REPLIES 2
amitchandak
Super User
Super User

@ExcelMonke , Days to respond or bucket should come from seperate table. that will offer better result. Measn has a dimension


CALCULATE (
DISTINCTCOUNT ( Data[OrderId] ),
FILTER (
ALLSELECTED ( DaystoRespond),
DaystoRespond[DaystoRespond] <= DaystoRespond( Data[DaystoRespond] )
)
)

 

Or use window function, make sure all groupby of visual are using in window's relation

 

You can alos use new visual calculation runningsum

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

 

Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hello,
Thanks for the suggestion. For some context: I do have an additional table that is for Day bucket. This links to my data table which has an additional column which rounds the 'Data'[DaystoRespond] column. 

So - this connection is a One:Many from DayBucket[Bucket] to Data[DaystoRespond_Bucket]. 

Unfortunatey, the above recommended DAX did not work. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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