Columna de acumulado

[mmontenegro]

Hello
I am trying to get the accumulated budget column, I have tried everything to make it dynamic since my budget has a fiscal year from June to May that is what I call a period, the fact is that I have managed to show them statically with this formula but no I manage to make it dynamic by selecting a month.

Measure:

The thing is that what I'm looking for is a dynamic accumulated as shown in the following image, when selecting, for example, the month of August, in the accumulated column it should return 3,333,590, otherwise it is returning the same result for the month, which is 770,122.

Same formula as above:

 

[tamerj1 · Accepted solution]

Hi @mmontenegro 
Please refer to attached file with the solution

In this case there are common slicers creating common outer filter context to two different visuals each having its own internal filter context. The best approach is to completely remove the "date" filter context and then build it in a way that serves both visuals. In order to do that I created a new column in the date table 

Orden YearMonth = Calendario[Orden año fiscal] * 100 + Calendario[Orden Mes Periodo]

The the following measure works for both visuals simultaneously 

Cumulative Real = 
VAR CurrentYear = MAX ( Calendario[Año Fiscal] )
VAR CurrentYearMonth = MAX ( Calendario[Orden YearMonth] )
VAR T1 = SUMMARIZE ( ALL ( Calendario ), Calendario[Año Fiscal], Calendario[Orden YearMonth] )
VAR T2 = FILTER ( T1, Calendario[Orden YearMonth] <= CurrentYearMonth && Calendario[Año Fiscal] = CurrentYear )
RETURN
    SUMX ( 
        T2, 
        VAR YeaMonth = [Orden YearMonth] 
        RETURN 
        CALCULATE([Real Mes], Calendario[Orden YearMonth] = YeaMonth, ALL ( Calendario ) )
    )

[mmontenegro]

Hi @tamerj1 

It is incredible, I am very grateful, everything works according to what I was looking for.

perform the same measurement with the accumulated budget and it works great!!

Thank you very much for the support @tamerj1 

[mmontenegro]

Hi @v-yueyunzh-msft 

please help, I attach my file, which contains all the data used, I have used your recommendation and I can not make it work.

https://cahsahon-my.sharepoint.com/:u:/g/personal/mmontenegro_cahsa_hn/ERBE4Xo7eVNAk1hzsAhxMfkBpPjKo...

[v-yueyunzh-msft]

Hi, @mmontenegro 

According to your description,when you write a measure that calculates the cumulative value, and then use the month to slice, "Total" is filtered, but you want to display the unfiltered sum. Right?

Here are the steps you can follow：

(1)This is my test data:

(2) To simulate your measure, I created [Acumulado Real] :

Acumulado Real = CALCULATE( SUM('Table'[Real Mes]) , FILTER ( ALLSELECTED('Canlendar'[Date]) , 'Canlendar'[Date]<= MAX('Canlendar'[Date])))

(3)We can create another to realize the replacement of “Total”:  “test”

test = IF( ISFILTERED('Table'[Master]) , [Acumulado Real] , CALCULATE([Acumulado Real] , ALL('Canlendar')))

(4)Then we can meet your need, the result is as follows :

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly