Incorrect Cumulative Total When Inserting second filter on Calculate

Hi Guys im having a hard time to understand why my cumulative Formula doesn’t Work.
The dax code is not calculating accurate cumulative totals when i insert the second filter

My Code is this:

ActivationsCumulative =

Var Activations = CALCULATE(COUNT(Activations[MSISDN]);FILTER(Activations;RELATED(‘Targeted Category’[Targeted Types])=“Activations”))
Return
IF (
DATE ( YEAR ( MAX ( ‘Datetable’[Date] ) ); MONTH ( MAX ( ‘Datetable’[Date] ) ); 1 )
<= DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ); 1 );
CALCULATE (
Activations;
FILTER (
ALL ( Datetable[Date] );
‘Datetable’[Date] <= MAX ( ‘Datetable’[Date] )
&& ‘Datetable’[Date] >= DATE ( 2019; 6; 1 )
);FILTER ( Activations; ‘Activations’[Assigned Customer BN] = 1 )
)

)

image

If i remove the filter for assigned customer bn it will show correct results but not the desired ones.

Here is some material to look at from the forum regarding Cumulative Totals. Please post a sample PBIX file so that we can better assist if you are still having issues.

https://forum.enterprisedna.co/t/mtd-ytd-showing-total-as-to-date-value-december-but-not-as-sum-of-all-mtd-ytd/3068

Thanks
Jarrett

Thanks for your Reply ojones,
I didnt find any related solution on the links above. I hope the PBIX file will shine some light in my problem.
Test2.pbix (2.0 MB)

Just had a look at your file, and it looks like the measure is doing exactly what you are telling it to. The # is different that the Activation on Assigned because you are only want to go from June 1st forward, that is why it is not including May. Do you want your measure to add up Cumulatively? What is the exact result you are looking for?

image

Thanks
Enterprise%20DNA%20Expert%20-%20Small

Yes i want to add up Cumulatively after 1/6/2019 but only when the [Assigned Customer BN] = 1.
So the result that i want to be aggregated is 23+32+39+38+39+66=237 For Totals and 23 for June, 55 for July, 94 for Aug etc… the second filter on the activation’s sheet is causing the miscalculation. If i remove it it will calculate correctly but not the desired result.

I found an issue with your calculated column called “Assigned Customer BN”. I created a measure that looks like this:

Assigned Customer BN Test =

SWITCH(TRUE(),

SELECTEDVALUE(CRM[AFM])= BLANK(),0,

SELECTEDVALUE(CRM[AFM])<> BLANK(),1,

BLANK())

Then I adjusted you ActivationsCumulative Measure to look like this:

ActivationsCumulative Test = 
    CALCULATE (
       [Activations],
        FILTER (ALLSELECTED(Datetable[Date]),
            'Datetable'[Date] <= MAX ( 'Datetable'[Date] )  && 'Datetable'[Date] >= DATE ( 2019, 6, 1 )
        ),FILTER(CRM,[Assigned Customer BN Test]=1))

Here is photo of what table looks like with the new measure:

image

Thanks
Enterprise%20DNA%20Expert%20-%20Small

1 Like

Here is another solution that will get rid of the total for December if you wish to:

ActivationsCumulative Test  = 
IF([Activations]= BLANK(),BLANK(),
    CALCULATE (
       [Activations],
        FILTER (ALLSELECTED(Datetable[Date]),
            'Datetable'[Date] <= MAX ( 'Datetable'[Date] )  && 'Datetable'[Date] >= DATE ( 2019, 6, 1 )
        ),FILTER(CRM,[Assigned Customer BN Test]=1)))

** I would also recommend that you create separate Measure Tables to store all of your measures. Your model was quite cluttered with the way the measures were stored. Sam has plenty of material on the site here that covers why it is a best practice to organize your model this way**

Thanks
Enterprise%20DNA%20Expert%20-%20Small

Thank you Ojones for everything, your suggestion works perfectly.
Is it obvious to you as to why the initial calculation wouldnt work?

It was obvious pretty quick that the issue was with how you were calculating your Calculated Column. I’ve been doing this for a while now, so much easier for someone like me to see this kind of error.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

Yes but still, i cant understand the error that i made. Why was it an error?
Am i missing something big in context transition?

I usually try to avoid calculated columns, unless absolutely needed. Calculated columns can hinder performance in some cases. The calculate column you had worked fine, but it did not allow for the correct cumulative results. In order to avoid things like this, I usually create a SWITCH Statement to get the same result.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

1 Like