Measure with Prior Year

Question

I have two measures that are working great as defined below. I am also trying to create a new measure that returns Prior Year Actives. The fact table and calendar table is not joined as the fact table has two date range fields, DateBeg and DateEnd.

Active = SUMX(EmployeeAsOf,EmployeeAsOf[ActiveRecord])

ActiveAsOfPY =

VAR CurrDate = SELECTEDVALUE('Calendar'[CalDate])

VAR Results = CALCULATE(

[Active],

CurrDate >= EmployeeAsOf[DateBeg],

CurrDate <= EmployeeAsOf[DateEnd])

RETURN

RESULTS

Tried to use DateAdd function to create a new measure but that is not valid.

VAR PYCurrDate = DATEADD(SELECTEDVALUE('Calendar'[CalDate]), -1, YEAR)

Appreciate any help on how I can achieve this and thank you!

amitchandak · Accepted Answer

JAKEDBG , Try Like
&nbsp;
ActiveAsOfPY =VAR CurrDate1 = SELECTEDVALUE('Calendar'[CalDate])VAR CurrDate = date(year(CurrDate1)-1, month(CurrDate1), day(CurrDate1))VAR Results = CALCULATE([Active],CurrDate &gt;= EmployeeAsOf[DateBeg],CurrDate &lt;= EmployeeAsOf[DateEnd])RETURNRESULTS

Forum Discussion

Measure with Prior Year

2 Replies

Related content

Remaining Balance Depends on Prior Month

Prior Month/Year Measures Not Working

Help with prior month measure

Current vs Prior Year Summation in a Measure

Immediately Prior focus period measure