cancel
Showing results for
Did you mean:
Highlighted
Established Member

## Cumulative value based on last "X" months selected

I have built out a report which allows a user to select a month and then select how many months prior of data they want to see, using a parameter for this. This is shown below.

The issue I have is I cannot seem to get a measure correct to show the values as a cumulative figure, I would like the measure to show the following in red.

Would anyone be able to help please? I have attached a sample file which should hopefully help: https://drive.google.com/open?id=1IFkuY7ZvqchRBoYsLfSl6_M_NA_Qk8JC

Thanks, Jake

1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

## Re: Cumulative value based on last "X" months selected

Basically, I added a new date filtering table, joined it to your fact table, and modified the measures.

The new months table has a filter date, included date, and difference between them. It will have approximately months^2 / 2 rows. You will probably want to add other columns, but it demonstrates the functionality.

Hope this helps,

Nathan

9 REPLIES 9
Senior Member

## Re: Cumulative value based on last "X" months selected

You can use the below expresion.

```Sales Running Total =
VAR mc = MAX('Calendar'[Date]) -- Your calendar dimension
VAR mf = CALCULATE(MAX(YourTable[Date]), ALL(YourTable)) >= mc
RETURN
IF(
mf,
CALCULATE(
[YourMeasure],
FILTER(
ALL('Calendar'[Date]),
'Calendar'[Date] <= mc
)
)
) ```

Hope this helps
Mariusz

Established Member

## Re: Cumulative value based on last "X" months selected

Thanks for the quick response @Mariusz

I tried your suggestion but unfortunately the values are not summing up cumulatively as shown below. I'm not sure where I have gone wrong.

Here is the measure I used so you can see for yourself

```Sales Running Total =
VAR mc = MAX(DateTableMonth[EndOfMonth]) -- Your calendar dimension
VAR mf = CALCULATE(MAX(opportunity[EndOfMonth]), ALL(opportunity)) >= mc
RETURN
IF(
mf,
CALCULATE(
[Profit (last n months)],
FILTER(
ALL(DateTableMonth[EndOfMonth]),
DateTableMonth[EndOfMonth] <= mc
)
)
) ```
Senior Member

## Re: Cumulative value based on last "X" months selected

Hi @jcarville

What table is your MonthYear Column?

Mariusz

Senior Member

## Re: Cumulative value based on last "X" months selected

HI @jcarville

This version will work if all the fields are in one table.

```VAR mc = MAX(opportunity[EndOfMonth])
RETURN
CALCULATE(
[Profit (last n months)],
FILTER(
ALL(opportunity),
opportunity[EndOfMonth] <= mc
)
)```

Hope this helps
Mariusz

Established Member

## Re: Cumulative value based on last "X" months selected

Hi @Mariusz , unfortunately all columns are not from the one table. The structure for showing the last "x" months of data from a certain month is a bit unusual.

You can see it for yourself in the file in this link: https://drive.google.com/open?id=1IFkuY7ZvqchRBoYsLfSl6_M_NA_Qk8JC

I have used the measure you have suggested before for cumulative measures.

New Contributor

## Re: Cumulative value based on last "X" months selected

Basically, I added a new date filtering table, joined it to your fact table, and modified the measures.

The new months table has a filter date, included date, and difference between them. It will have approximately months^2 / 2 rows. You will probably want to add other columns, but it demonstrates the functionality.

Hope this helps,

Nathan

Established Member

## Re: Cumulative value based on last "X" months selected

@natelpeterson, that is fantastic and exactly what I was after. Thanks for your help.

Can you explain anymore why I required another date filtering table?

New Contributor

## Re: Cumulative value based on last "X" months selected

The "Profit (last n months)" measure was changing the filter context to force inclusion of a certain set of months. There may be a way to manipulate it further so that you can get a running total.

However, I prefer to prepare the model so that measures are more straightforward. The new table is created with the intention to simplify and make the selections more intuitive.

Also, I'd recommend removing unneeded tables/columns by either deleting or hiding.

Cheers!

Nathan

Established Member

## Re: Cumulative value based on last "X" months selected

@natelpeterson, top man thanks for that.