Problem with accumulation of a measure

[Syndicate_Admin]

Hello everyone!!!!

I'm wanting to accumulate a measure and it's not working for me. Below I present the table. There the column that does not work for me is what I call 'Accumulated Variation', this column what I should do is to accumulate by year what the column 'Monthly Variation' shows (0.00%+5.56%+0.00%+9.21%+ ....)

I have 3 measures created:

1. Index = SUM(Table[Index])
2. Monthly Variation =

VAR IndiceMesAnterior =

CALCULATE([Index],DATEADD(Calendar[Date],-1,MONTH))

RETURN

DIVIDE(([Index]-IndexMesAnterior), IndexMesAnterior)

1. Accumulated Variation =

CALCULATE([Monthly Variation],DATESYTD(Calendar[Date]))

In measure 2 what I do is calculate the monthly variation that there was with the 'Index' field. This measure gives the correct information.

I have the problem in measure 3. To this extent I want to accumulate by year what is shown in measure 2. Obviously the way I am accumulating that I am doing in measure 3 is not correct.

The result of measure 3 ('Cumulative Variation'), should give me this result:

Each of these results is the cumulative sum per year of measure 2 ('Monthly variation').

I share Power Bi file link where this project is so that you can access:

file://CARLOS-MARI-UA/Users/Carlos%20Mar%ED/Google%20Drive/Prueba.pbix

Thank you very much for the help you can give me.

Carlos

[v-eqin-msft · Accepted solution]

Hi @Syndicate_Admin ,

Please try:

Accumulated Variation =
IF (
    MIN ( Calendar[Datekey] ) <= CALCULATE ( MAX ( Table[DateKey] ), ALL ( Table ) ),
    CALCULATE (
        [Total ],
        FILTER (
            ALL ( Calendar[Datekey] ),
            Calendar[Datekey] <= MAX ( ( Calendar[Datekey] ) )
        )
    ),
    BLANK ()
)

If it does not work , please provide more details about your table.

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

[v-eqin-msft]

Hi @Syndicate_Admin ,

It seems that your issue has been resolved,right?

Best Regards,
Eyelyn Qin

[Syndicate_Admin · Accepted solution]

Hello! Issue resolved!!!! Thanks a lot!!!!!!!

[v-eqin-msft]

Hi @Syndicate_Admin ,

Sorry for my late reply. But I could not get your pbix file.You may refer to this blog to learn  How to provide sample data in the Power BI Forum

Best Regards,
Eyelyn Qin

[Syndicate_Admin]

Thank you Evelyn for your willingness to help me.

I pass the link:

https://educacionadventistaorg-my.sharepoint.com/:u:/g/personal/carlos_mari_adventistas_org_ar/ERowC...

[v-eqin-msft · Accepted solution]

Hi @Syndicate_Admin ,

Please try:

Accumulated Variation =
IF (
    MIN ( Calendar[Datekey] ) <= CALCULATE ( MAX ( Table[DateKey] ), ALL ( Table ) ),
    CALCULATE (
        [Total ],
        FILTER (
            ALL ( Calendar[Datekey] ),
            Calendar[Datekey] <= MAX ( ( Calendar[Datekey] ) )
        )
    ),
    BLANK ()
)

If it does not work , please provide more details about your table.

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

[Syndicate_Admin]

Evelyn, thank you for your reply.

I tried the code you sent and it works well if I use to accumulate the measure I call 'Index' that its DAX code is'Index = sum(Table[Index])'. The problem I have is that what I need to accumulate is another column that is also a measure that I call 'Monthly Variation' (in the first message is the code of this measure). When I use the code you sent me and only change by this measure, there everything is decompressed and gives any value.

Obviously the measure 'Monthly Variation', which is the measure that I need to accumulate, makes the answer not the natural one, as it happens well when using the other measure ('Index') with the code you sent.

I share the URL of the project so you can see to try it directly (I share the URL to place in the browser since it gives me error when I try to do it as a link access):

file://CARLOS-MARI-UA/Users/Carlos%20Mar%ED/Google%20Drive/Prueba.pbix

Thank you very much for your help.

Best regards

Carlos.

[Syndicate_Admin]

Thank you very much for the help shared. I tried the code, in the two alternatives you mention, and it doesn't work.

First create the measure

d = CALCULATE(SUM(Table[Index]),Filter(all('Calendar'), 'Calendar'[Date]<= Max(Calendar[Date])))

and then the measure

Accumulated Variation = DIVIDE(SUM(Table[index]),[d])

Then I tried modifying measure d

d = CALCULATE(SUM(Table[Index]),Filter(all('Calendar'), 'Calendar'[Date]<= EOMONTH(Max(Calendar[Date]),-1) ))

but it didn't work either. I share a screen of the result of this last measure:

If there were any other ideas I would be very grateful.

[amitchandak]

@Syndicate_Admin , Create a measure like

D = CALCULATE(SUM(Table[Index]),Filter(all('Calendar'), 'Calendar'[Date]< = Max(Calendar[Date])))

or
D= CALCULATE(SUM(Table[Index]),Filter(all('Calendar'), 'Calendar'[Date]< = eomnoth(Max(Calendar[Date]),-1) ))

then divide

divide(SUM(Table[Index]),[D])

[Syndicate_Admin]

Thank you very much for the help shared. I tried the code, in the two alternatives you mention, and it doesn't work.

First create the measure

d = CALCULATE(SUM(Table[Index]),Filter(all('Calendar'), 'Calendar'[Date]<= Max(Calendar[Date])))

and then the measure

Accumulated Variation = DIVIDE(SUM(Table[index]),[d])

Then I tried modifying measure d

d = CALCULATE(SUM(Table[Index]),Filter(all('Calendar'), 'Calendar'[Date]<= EOMONTH(Max(Calendar[Date]),-1) ))

but it didn't work either. I share a screen of the result of this last measure:

If there were any other ideas I would be very grateful.