Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jcarville
Skilled Sharer
Skilled Sharer

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
Anonymous
Not applicable

@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

View solution in original post

9 REPLIES 9
Mariusz
Community Champion
Community Champion

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

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
        )
    )
) 

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

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.

Anonymous
Not applicable

@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

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

 

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

Anonymous
Not applicable

@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

@Anonymous, top man thanks for that.

Hi @jcarville 

What table is your MonthYear Column?

Mariusz 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.