cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jcarville Established Member
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.

 

image1.png

 

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.

image2.png

 

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
natelpeterson New Contributor
New Contributor

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

@jcarville  - 

Please see this pbix.

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
Mariusz Senior Member
Senior Member

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

Hi @jcarville 

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

jcarville Established Member
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.

 

Untitled.png

 

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
        )
    )
) 
Mariusz Senior Member
Senior Member

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

Hi @jcarville 

What table is your MonthYear Column?

Mariusz 

Mariusz Senior Member
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

jcarville Established Member
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.

natelpeterson New Contributor
New Contributor

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

@jcarville  - 

Please see this pbix.

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

jcarville Established Member
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?

natelpeterson New Contributor
New Contributor

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

@jcarville  - 

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

jcarville Established Member
Established Member

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

@natelpeterson, top man thanks for that.