cancel
Showing results for
Did you mean:
Regular Visitor

## Cumulative Total for selected period in prior year

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.

1 ACCEPTED SOLUTION Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.   