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

[Dunner2020]

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

[v-kelly-msft]

Hi  @Dunner2020 ,

Is your solved now?If not,could you pls check my last reply?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft · Accepted solution]

Hi  @Dunner2020 ,

Could you pls check whether your date table is a contiguous date table?As tested here,it works fine:

Would you pls check my .pbix file and advise me whether it is what you need?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Dunner2020 , Can you try datesytd with Date table like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))

where 3/31 in an indicator where year-end to set up the start date for ytd.

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA