Forum Discussion

prasanna11289's avatar
prasanna11289
Regular Visitor
3 years ago

Cumulative Sum doesn't consider blank values

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])
)
)
)
)
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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

  • USER99's avatar
    USER99
    Regular Visitor
    2 years ago

    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!