Cumulative Total for selected period in prior year

[Gensetter]

I am running into issues with cumulative totals and was wondering if anyone else has solved this issue:

I am trying to create a chart which shows the cumulative total for the selected perioed, the target for the selected period, and the cumulative PY total for the selected period. I can get it all to work except for the PY cumulative total.

Here is a chart to illustrate my progress and problem.

The cumulative total that works (the green line) uses this DAX Expression:

Order Net YTD (G) =
VAR EndDate =
MAX ( DIM_DATE_TABLE[Date] )
VAR CumulativeTotalCY =
CALCULATE (
SUM ( SalesFact[Net Value] ),
FILTER (
ALLSELECTED ( DIM_DATE_TABLE ),
DIM_DATE_TABLE[Date] <= EndDate
)
)
RETURN
IF (
MAX ( DIM_DATE_TABLE[Date] ) <= [Date of Most Recent Data],
CumulativeTotalCY,
BLANK ()
)

And the little black squigly line at the bottom of the chart is the one that is broken. I can't get it to be a cumulative total of the prior year.

That dax expression is simply 

CALCULATE( [Order Net YTD (G)] , SAMEPERIODLASTYEAR(DIM_DATE_TABLE[Date]))

I'm not using the TotalYTD because the business requirement is the user is able to pick the start and end date.
The only filter applied to the page is Year = 2019.

[v-lili6-msft · Accepted solution]

hi, @Gensetter 

For your case, you could use this formula to add a PY measure

Measure 3 = CALCULATE(CALCULATE( [Order Net YTD (G)] , SAMEPERIODLASTYEAR(DIM_DATE_TABLE[Date])),
FILTER (
ALLSELECTED ( DIM_DATE_TABLE ),
DIM_DATE_TABLE[Date] <= MAX(DIM_DATE_TABLE[Date])
))

Result:

Best Regards,

Lin