Running Total of percent growth measure

[nleuck_101]

Hello All,

I have a measure where I calculate the Avg Monthly Payment Growth % and I would like to calcuate the running total of that measure. I'm not really finding what I want online and any help would be greatly appreciated. I did try using a visual calcuation which seemed to work but I would like to know how to do this in dax. I've done a running total on whole numbers before but not percentages.

[danextian]

Hi @nleuck_101 

It looks like your percentage measure is something like dividing Value A by Value B, and what you’re aiming for is a running total that sums the already calculated yearly percentages, rather than recalculating the division of total Value A over total Value B across all years (or up to the current/max year).

Ensure to use a dedicated dates table and replace the referenced column with Year. Please try the attached pbix.

[nleuck_101]

@danextian 

I only want to look at the year starting at 2019 through whatever the current year is.

[danextian]

You can add an extra filter

[nleuck_101]

@danextian 

Yeah no matter what I try it's not working. The right column is your formula.

[danextian]

Did you have a look at the pbix in  my original response?  It uses a separate/dedicated dates dimension table. If that doesn't work, please provide a sanitized (confidential data removed)  copy of your pbix.

[nleuck_101]

@danextian 

I have a separate date table as well. I can not provide you a pbix file. I would have to create a new file with fake data and don't have time to do that.

[v-hjannapu]

Hi @nleuck_101,
As mentioned by @danextian , could you please share the PBIX file? This will help us better understand your requirements. Once we have it, we can provide you with an accurate solution.

Regards,
Community Support Team.

[v-hjannapu]

Hi @nleuck_101,
Just following up on this request. Could you please share the PBIX file or a sample file with dummy data if possible? It will help us reproduce the issue from our side and provide a solution according to your exact requirement.

Regards,
Community Support Team.

[krishnakanth240]

Hi @nleuck_101 

Can you try this

Running Growth % =

VAR CurrentYear = MAX('Date'[Year])

VAR CumCurrent =

 CALCULATE([Total Payments],

 FILTER(ALL('Date'), 'Date'[Year] <= CurrentYear) )

VAR CumPrevious =

CALCULATE( [Total Payments],

FILTER(ALL('Date'), 'Date'[Year] <= CurrentYear - 1))

RETURN

DIVIDE(CumCurrent - CumPrevious, CumPrevious)

[nleuck_101]

@krishnakanth240 

I'm not sure what that did but definitely did not work.

[krishnakanth240]

Hi @nleuck_101 

Please share sample data of tables in text format or you can share a pbix file over onedrive link providing access to link 

[Shai_Karmani]

You can do this with a classic running total over your existing measure. The pattern is CALCULATE plus a filter on your year column, with ALL on that column so the filter context is reset on each row of the visual.

Replace 'Date'[Year] with whatever your actual year column is called.

Running Avg Monthly Payment Growth % =

VAR CurrentYear = MAX('Date'[Year])

RETURN

CALCULATE(

    [Avg Monthly Payment Growth %],

    'Date'[Year] <= CurrentYear,

    ALL('Date'[Year])

)

Drop that into the same table or matrix you were using for the visual calculation and you should see the cumulative percentages year by year. If the year column lives directly on the fact table rather than a separate date table, just point the FILTER at that column and use ALL on it the same way.

If this works for you, kindly mark it as the solution and give a thumbs up.

Best,

Shai Karmani

[nleuck_101]

@Shai_Karmani 
This is what I get but it doesn't seem correct. If I do a visual calculation I get what I expect but I want to it in DAX and not a visual calculation (the last number is the visual calculation).

[Ashish_Mathur]

Hi,

Please share the download link of the PBI file.