Forum Discussion

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Cumulative measures does not show value when filter is not selected.

Hi there, 

I have a column in the Fact table called ' 30 Mins Normalized Value'. I created a measure that calculates the sum of [30 Mins Normalized Value] as follow:

 

Normalized Value = SUM('Fact Table'[30 Mins Normalised Value])
 
In my scenario, regulatory year starts on 1st of April and ends on 31st March. I got data from 1st of April 2020 and till to date. So it is more than a year of data. I wanted to create two measures that create the cumulative sum of last regulatory year and another measure that calculates the cumulative sum of the current regulatory year. My measures are as follow:
last year Cumulative Normalized Value =
var CurrentDate = MAX('Fact Table'[Date])
Return
SUMX(
FILTER(ALL('Date'),'Date'[Date]<=CurrentDate && 'Date'[RY Month Number]<=MAX('Date'[RY Month Number])
&& CurrentDate < [Current_RY_Yr_Strt] ), [Normalized Value])
 
Current Cumulative Normalized Value =
var CurrentDate = MAX('Fact Table'[Date])
Return
SUMX(
FILTER(ALL('Date'),'Date'[Date]<=CurrentDate && 'Date'[RY Month Number]<=MAX('Date'[RY Month Number])
&& CurrentDate >= [Current_RY_Yr_Strt] ), [Normalized Value])
 
[Current_RY_Yr_Strt] defined as follow:
Current_RY_Yr_Strt =
Var month = MONTH(TODAY())
RETURN
IF(month>3 && month <= 12, DATE(YEAR(TODAY()),4,1),DATE((YEAR(Today())-1),4,1))
 
The problem is that when I show two cumulative mesaures on table visual, last year cumulative Normalized value measure display blank value and current cumulative Normalized Value display value of 12 months as shown in the picture:
 

The expected output is Cumulative Normalised value only display value for April and last year Cumulative measure show value of all 12 months. When I clicked on RY21 (i.e. last regulatory year) filter then it shows the value as shown in the picture:

 

Now, last year cumulative value shows all 12 months' value but the current cumulative normalized value measure does not show any value.  

 

Expected Output: when there is no filter on RY selected, both measures should display the values. [Last year cumulative normalized value] shows value for all 12 months and [Current Cumulative Normalized Value] should show the value of only April. Could anyone guide me where am I making the mistake?

 

Sample file can be download from here

 

 

Need Help
Show and Tell
Tips and Tricks

3 Replies