Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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.
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.
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?
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |