Cumulative Sum doesn't consider blank values

[prasanna11289]

Hi Team,

In my Power BI project, I'm facing an issue related to Cumulative Lead counts for quarterly data at the Rep level. I've managed to calculate the cumulative counts successfully, but there's an issue with handling weeks where no values exist. To clarify, when certain weeks between Week 1 to 13 have no leads assigned, my DAX measure returns a blank instead of considering them in the calculation. How can I address this issue?

Please find the below snip.

In the table on the left, you'll see a standard total, while the one on the right represents a cumulative total. On row number 2, my ideal outcome would be for 2024-w28 to display the number 64 as well. However, it currently returns a blank value because there is no W28 record for the specified rep.

Here is the DAX I used for this measure -

Cumulative_Lead_Count =

SUMX(

VALUES(SoapData[lead_owners_nm]), -- Iterate through unique lead_owners_nm values

IF(

ISBLANK(MAX(SoapData[fisc_quater_val])) ||

ISBLANK(MAX(SoapData[fisc_wk_end_dt])) ||

ISBLANK(MAX(SoapData[fisc_wk_val])),

BLANK(),

CALCULATE(

DISTINCTCOUNT(SoapData[lead_id]),

FILTER(

ALLSELECTED(SoapData),

SoapData[fisc_quater_val] <= MAX(SoapData[fisc_quater_val]) &&

SoapData[lead_owners_nm] = EARLIER(SoapData[lead_owners_nm])

)

)

)

)

[USER99]

I was having the same problem and what solved it for me was removing the relationships with any ambiguous path between the Calendar and the Date column. Put all relationships to the Calendar as uni-directional. It means that the fact that there is no value in a month in your base, doesnt make that month to be not filtered in the Calendar. Try it!

[amitchandak]

@prasanna11289 , You should create a rank column on YYYYWW column

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

then have a measure like this or use Windows function

Example

CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])))

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