# Cumulative measure does not work when apply filter

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:sample8.pbix (1.0 MB)

Hi @leo_89,

I think you have posted old file which does not have Date table & Measures. Can you upload the latest file?

Kind Regards,
Hafiz

@hafizsultan, Sorry. I uploaded recent file,

Hi @leo_89,

Thanks for posting updated file. Minor error and after correcting, results seem fine.

sample_Leo.pbix (1.0 MB)

@hafizsultan, thanks for the reply. How could we leave blank values for the months that are not coming yet? For example, the Current cumulative Normalized Value shows the value for May, June, and so on. It should display the blank values for those months.

Hi @leo_89,

You just need to remove ALL from the FILTER as ALL is removing filter context from Dates and that is why same value is appearing in all months. If you remove ALL, below will be the output:

1 Like

Hi @leo_89, did the response provided by @hafizsultan help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

1 Like