Accumulative measure with filters

[ANDREIA_TESSARI]

Hi! 

I'm having trouble to get the right result from some accumulate measures.

That's the deal, I have some measures that are basically one measure of SUM but with a lot of FILTERS and I need the accumulative result per month of each measure. 

First measure:

ISP OEM = SUM(ISP[Valor]) * 5.38

Filters measure:

ISP Accepted = CALCULATE([ISP OEM], FILTER(ISP, ISP[Acquisition Status] = "Accepted"))

ISP Planning Relevant = CALCULATE(
[ISP OEM],
'ISP'[Acquisition Status] = "Quoted" ||
'ISP'[Acquisition Status] = "Sell!" ||
'ISP'[Acquisition Status] = "New", FILTER(ISP, ISP[Competitor Name] = "SEG"))

Finally, I did a CALCULATE measure to both using FILTER and "Date <= MAX(date)" in each filter measure to get the accumulative result. 

Final measure:

ISP Planning Relevant ACC = CALCULATE([ISP Planning Relevant],
FILTER(ALL(ISP[DOS Data].[Date]), ISP[DOS Data].[Date] <= MAX(ISP[DOS Data].[Date]) ) )

ISP Acceped ACC = CALCULATE([ISP Accepted],
FILTER(ALL(ISP[DOS Data].[Date]), ISP[DOS Data].[Date] <= MAX(ISP[DOS Data].[Date]) ) )

I don't know what is wrong with my filter, but it was supposed to get the values from each month and add it to the current month value, but It's not what it is doing. Looks like it is ignoring the filter.

I would appreciate some help.

Thanks!

[Ashish_Mathur]

Hi,

Simpify your measures to:

ISP Accepted = CALCULATE([ISP OEM],ISP[Acquisition Status] = "Accepted")

ISP Planning Relevant = CALCULATE([ISP OEM],('ISP'[Acquisition Status] = "Quoted" ||'ISP'[Acquisition Status] = "Sell!" ||'ISP'[Acquisition Status] = "New"), ISP[Competitor Name] = "SEG")

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by Month number.  Create a relationship (Many to One and Single) from the DOS date column to the Date column of the Calendar Table.  To your visual, drag Year and Month name from the Calendar Table.  Write these measures

ISP Planning Relevant ACC = CALCULATE([ISP Planning Relevant],DATESYTD('Calendar'[Date],"31/12"))

ISP Acceped ACC = CALCULATE([ISP Accepted],DATESYTD('Calendar'[Date],"31/12"))

Hope this helps.

[ANDREIA_TESSARI]

Hi!

I did it as you said. But I got another problem. 

1- ISP Acceped ACC

It seams that it is not accumulting, only filtering. You can see that as the number chance to 20M instead of adding it to the count. 

2-  ISP Planning Relevant ACC

Same thing on both measures. 

[Ashish_Mathur]

Hi,

Modify the measure to

ISP Planning Relevant ACC = CALCULATE([ISP Planning Relevant],DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MAX('Calendar'[Date])))

ISP Acceped ACC = CALCULATE([ISP Accepted],DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MAX('Calendar'[Date])))

Hope this helps.

[lbendlin]

ALL() removes the filter context that you have previously constructed. Use something more appropriate.