How to have cumulative total stop showing/repeating after reaching max cumulative total?

DAX DAX Calculations
1

Hello PowerBI experts, hope you all are well and safe!

I am stuck with fixing an issue with cumulative totals, and any guidance would be great! I have matrix showing cumulative totals for each month by day. The issue here is that after the max cumulative total is reach for each month, the matrix continues to show this value for subsequent days but I would rather have these records blank to indicate no activity. The matrix I have is as follows:

unnamed
unnamed813Ă—617 278 KB

The DAX query for each month is very similar, only difference is the month name. The DAX measure for April is as follows:

Apr-20 =
CALCULATE
(
SUM(RemittanceData[TRANSACTIONAMOUNT])
,FILTER
(
ALLSELECTED(RemittanceData)
,RemittanceData[BusinessDayOfMonth] <= MAX(RemittanceData[BusinessDayOfMonth])
&& RemittanceData[ReportMonthFormat] = “APR-20”
)
)

What modifications to the DAX query can I make to return no values for days the cumulative total didn’t increase? I looked at multiple places for a solution, but I didn’t find anything really I could use to modify my DAX query. Please let me know if my question needs any clarification. Any help would be highly appreciated, thank you very much!

2

Hi @GrapeApe561, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

@GrapeApe561,

I think these two videos below will give you what you need to solve this one:

https://forum.enterprisedna.co/t/cumulative-totals-only-up-to-specific-dates-dax-tutorial-for-power-bi/2987

https://forum.enterprisedna.co/t/show-actual-results-vs-targets-only-to-last-sales-date-dax-cumulative-totals/2968

I hope this is helpful.

  • Brian
1 Like
4

Hi @GrapeApe561,

Could you give this a try.

Cum sales by Month Unique = 
VAR CumSales =
CALCULATE(
    SUM( RemittanceData[TRANSACTIONAMOUNT] ),
    FILTER(
        ALLSELECTED( RemittanceData ),
        RemittanceData[BusinessDayOfMonth] <= MAX( RemittanceData[BusinessDayOfMonth] )
        && RemittanceData[ReportMonthFormat] = "APR -20"
    )
)

VAR PrevCum =
CALCULATE(
    SUM( RemittanceData[TRANSACTIONAMOUNT] ),
    FILTER(
        ALLSELECTED( RemittanceData ),
        RemittanceData[BusinessDayOfMonth] <= (MAX( RemittanceData[BusinessDayOfMonth] ) -1)
        && RemittanceData[ReportMonthFormat] = "APR -20"
    )
)
RETURN 

IF( CumSales = PrevCum, BLANK(), CumSales )
5

Hi Melissa,

I feel your answer is very close to the solution. Please correct me if I am mistaken, but in your code the “CumSales” variable hasn’t been defined. Should I replace “Cum sales by Month Unique” in your code with “CumSales.” Thank you so much for your help, I appreciate it more than be explained with words!

6

I didn’t copy that right, apologies.
Changed it in the previous post, that should be fine now.

1 Like
7

Thank you so much! Because of your solution I was able to impress my boss :slightly_smiling_face:

1 Like