Cumulative Sum for Previous Year Sales also includes Sales of Year before

[Anonymous]

Hi,

I have created a visual that is showing me the cummulative budget, landing (sales for the past, forecast for the future) and the previous year sales (plus also the latest budget revision). I created the measure for the cummulative previous year sales during the last fiscal year (Jul 19 to Jun 20). Then it worked fine. Now I added the current fiscal year and a slicer to choose between the fiscal years. The calculation for last fiscal year still works fine:

THe black line shows the previous year sales of fiscal year 19 (July 18 to June 19). It starts at zero and adds up the sales from FY19.

If I slice for the current fiscal year, though, it starts with the cummulative sales of June 19 and adds the sales of FY20:

My formula for calculating the cummulative previous year sales is:

m.**bleep**.ActualsLTPY =

CALCULATE([m.ActualsLT],

FILTER(

ALL(Date_DIM),

SAMEPERIODLASTYEAR(Date_DIM[Date ID].[Date])

&& Date_DIM[Date ID] <= MAX(Date_DIM[Date PY])))

It would be massively appreciated, if anyone could tell me, how to cummulate only the previous year sales data for the fiscal year I am slicing for.

Many thanks

Malte

[amitchandak · Accepted solution]

@Anonymous , You can use datesytd with year-end date parameter.

example

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

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

[Anonymous]

Many thanks! It does exactly what I was looking for!