I have been trying to calculate the Previous Year amount YTD with no success.
I have been using the following Measure PY actuals to calculate the PY amount in general:
PY actuals =
CALCULATE(
SUM(fct_transaction[Value]),
SAMEPERIODLASTYEAR(dim_calendar[End of Month]))
Then I have been using the following measure PY actuals YTD to calculate the Precious Year amount YTD
PY Actuals YTD =
VAR _referencedate = CALCULATE(MAX(fct_transaction[End of Month]), fct_transaction[Scenario] = "ACT")
VAR _latestYear =
YEAR (_referencedate)
VAR _latestMonth =
MONTH(_referencedate)
VAR _LatestQuarter = QUARTER(_referencedate)
RETURN
IF(SELECTEDVALUE('time aggregation'[time aggregation]) = "YTD",
CALCULATE(TOTALYTD([PY actuals], 'dim_calendar'[End of Month]),
FILTER(dim_calendar,
dim_calendar[Year] = _latestYear)))
In the table fct_transaction there are both Actuals and Previous Year data. The Fact table transaction it is directly connected to the dimension table dim_calendar
Now when I use the PY Actuals YTD the result obtained it is the full year amount of Actuals+Precious year, which is wrong.
I also tried to add -1 to get the latest year as in the example below:
PY Actuals YTD =
VAR _referencedate = CALCULATE(MAX(fct_transaction[End of Month]), fct_transaction[Scenario] = "ACT")
VAR _latestYear =
YEAR (_referencedate) -1
VAR _latestMonth =
MONTH(_referencedate)
VAR _LatestQuarter = QUARTER(_referencedate)
RETURN
IF(SELECTEDVALUE('time aggregation'[time aggregation]) = "YTD",
CALCULATE(TOTALYTD([PY actuals], 'dim_calendar'[End of Month]),
FILTER(dim_calendar,
dim_calendar[Year] = _latestYear)))
But no result are given.
I want to obtain the Previous Year YTD amount. What’s wrong in those measure? What must be corrected?