Cummulative sum of previous period not working properly

[mba]

Hi,

I have a problem while constructing a cummulative sum chart for a comparison with a target&previous period numbers.

I have defined revenue as:

Total Revenue DK = Calculate(sum(Orders[SubTotal]), some irrelevant filters here)

then the previous year's revenue:

Prev Year Revenue DK = CALCULATE([Total Revenue DK], SAMEPERIODLASTYEAR('Calendar'[Date]))

and then I proceeded to create the running total of previous year's revenue:

Prev Year Revenue DK RT = 
CALCULATE(
	[Prev Year Revenue DK],
	FILTER(
		ALLSELECTED('Calendar'[Date]),
		'Calendar'[Date] <= MAX('Calendar'[Date])
	)
)

Here's the issue with that:

the today's number (18/11/2021 in this case) for a corresponding last year's running total is literally the total sum of the whole current month - hence the sudden rise.

The chart filtering is set for the current month.

What should I do in this case?

[v-janeyg-msft]

Hi, @mba 

Not sure if it’s a problem with time intelligence functions.

You can try:

Prev Year Revenue DK = CALCULATE([Total Revenue DK], DATEADD('Calendar'[Date], -1, year))

 Not clear about your visual context.

You can try:

Prev Year Revenue DK RT = 
CALCULATE(
	[Prev Year Revenue DK],
	FILTER(
		ALL('Calendar'[Date]),
		'Calendar'[Date] <= MAX('Calendar'[Date])
	)
)

You can also try to merge there measures to one measure.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Community Support Team _ Janey

[amitchandak]

@mba , Assume you have cumm measure

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

year behind cumm

Cumm Sales Tr 1 = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,year)))

[mba]

According to your advice, I changed it to:

Prev Year Revenue DK - RT = 
CALCULATE(
	[Total Revenue DK],
	FILTER(
		ALLSELECTED('Calendar'[Date]),
		'Calendar'[Date] <= MAXX('Calendar', DATEADD('Calendar'[Date], -1, YEAR))
)
)

... but now the values are completely missing:

It is due to the fact that dateadd() put it back one year, so there's no way that it is higher than current date. Hence no values.

PS. It's all on the same graph as these from current year.

[v-janeyg-msft]

@mba  Any updates?

I haven't received your reply and don't know your current situation.

If you still need help, please feel free to ask me.

Best Regards,
Community Support Team _ Janey