Forum Discussion
Post PartisanRolling Total not showing as needed - please help
Hi,
I need to create two measures as follows:
Measure 1) to show the rolling total up to the last (max) date as specified in the matrix.
Measure 2) to show the rolling total for all dates, whether selected in the matrix or not, up to the last (max) date as specified in the matrix.
The Date table (dimDate) has two relationships to the Agreement table (agreement) in the data model.
The active relationship is dimDate[Date] to agreement[Execution Date].
The inactive relationship is dimDate[Date] to agreement[Received Date].
The relevant code snippet is (but it isn't correct):
The [__Number of Distinct Agreements] is simply DISTINCTCOUNT( agreement[agreementid] ).
The output is to be shown in a Matrix visual as below (although the screenshot below doesn't have the correct figures - hence this post):
The above Matrix has the date heirarchy of Financial Year (FY), Financial Quarter (FQ), and Month Name (MN), all from the dimDate table. You'll note only the Financial Year is shown in the Matrix above. The measure needs to calculate for FY, FQ, and MN when they are expanded.
The agreement[Team Name] is across the columns in the Matrix.
By way of slicers, the report user will have the ability to selected/deselect FY, FQ, and MN, as well as Team Name, as well as other attributes.
The needed measures should show in the Matrix as follows:
Measure 1) - a rolling total for the numbers actually selected (and shown) in the matrix visual.
FY Res
2024-25 64
2023-24 49
2022-23 35
2021-22 19
Measure 2) - peforming a rolling total for all years whether selected or not.
FY Res
2024-25 2187
2023-24 2172
2022-23 2158
2021-22 2142
This is becuase prior to 2021-22 there was a total of 2123 agreementids. This is what I mean by this measure needs to perform a rolling total for all years whether selected or not.
Please can you help me achieve this? Thanks.
You can try
Rolling Total Selected Dates = VAR MaxDate = MAX ( __dimDate[Date] ) VAR Result = CALCULATE ( [__Number of distinct Agreements], USERELATIONSHIP ( __dimDate[Date], agreement[Received Date] ), FILTER ( ALLSELECTED ( __dimDate[Date] ), __dimDate[Date] <= MaxDate ) ) RETURN Resultand
Rolling Total All Time = VAR MaxDate = MAX ( __dimDate[Date] ) VAR Result = CALCULATE ( [__Number of distinct Agreements], USERELATIONSHIP ( __dimDate[Date], agreement[Received Date] ), __dimDate[Date] <= MaxDate ) RETURN ResultChange measure 1 to be
Rolling Total Selected Dates = VAR MaxDate = MAX ( __dimDate[Date] ) VAR Result = CALCULATE ( [__Number of distinct Agreements], USERELATIONSHIP ( __dimDate[Date], agreement[Received Date] ), FILTER ( ALLSELECTED ( __dimDate ), __dimDate[Date] <= MaxDate ) ) RETURN ResultThat is moving the ALLSELECTED from the column to the full table.
4 Replies
johnt75Super User
You can try
Rolling Total Selected Dates = VAR MaxDate = MAX ( __dimDate[Date] ) VAR Result = CALCULATE ( [__Number of distinct Agreements], USERELATIONSHIP ( __dimDate[Date], agreement[Received Date] ), FILTER ( ALLSELECTED ( __dimDate[Date] ), __dimDate[Date] <= MaxDate ) ) RETURN Resultand
Rolling Total All Time = VAR MaxDate = MAX ( __dimDate[Date] ) VAR Result = CALCULATE ( [__Number of distinct Agreements], USERELATIONSHIP ( __dimDate[Date], agreement[Received Date] ), __dimDate[Date] <= MaxDate ) RETURN Result- D_PBI
johnt75 - I think I was premature in marking it as the solution. Thanks for your help.
Both of your measures are returning the same result. The below screenshot is the resultset for both of your measures (I've filtered the Team Name to be just 'Res').The above output is what I require for Measure 2. However, for Measure 1, I required the rolling total for only the years selected. Below are the numbers for each individual FY.
So Measure 1 would be:
FY Res
2024-25 64
2023-24 49
2022-23 35
2021-22 19
2009-10 0
It's only rolling totalling the years selected. Can you help please? Thanks.- johnt75
Change measure 1 to be
Rolling Total Selected Dates = VAR MaxDate = MAX ( __dimDate[Date] ) VAR Result = CALCULATE ( [__Number of distinct Agreements], USERELATIONSHIP ( __dimDate[Date], agreement[Received Date] ), FILTER ( ALLSELECTED ( __dimDate ), __dimDate[Date] <= MaxDate ) ) RETURN ResultThat is moving the ALLSELECTED from the column to the full table.