Incorrect Cumulative Total When Inserting second filter on Calculate

[giomil]

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 )
)

)

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

[JarrettM]

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

Cumulative count

Hi, I need little guidance on the below scenario. please can you help I have registrations for events. need to show cumulative count of registrations for selected year. the cumulative count should start in descending order of weeks to go column. attached is a sample data. Sample.xlsx (11.1 KB)

Reverse cumulative sum

Hi Sam, I have worked my way through this rather complicated formula and it is quite brilliant but has a bit of a shortcoming. Firstly, the total is wrong. In this example, it should be 426, not 412. The second thing is that I need to reference the result of this in other measures, but now since we have removed the month filter from the calculation, I am getting incorrect results. As a rather crude example, say I need “Chosen Sales Values” Chosen Sales values = [Reverse Cumulative] If I pu…

Thanks
Jarrett

[giomil]

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)

[JarrettM]

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?

Thanks

[giomil]

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.

[JarrettM]

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:

Thanks

[JarrettM]

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

[giomil]

@JarrettM

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))

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

[JarrettM]

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

[giomil]

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

[JarrettM]

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