I am trying to pull the prior year MTD value for each month seperately. This is to calculate YoY for each month and display it in a table.
I am trying to use the below measure to get the prior year MTD value but it always returns full month value instead of the MTD one. Any hint on where I am going wrong here ?
Formulas that I have tried:
May PY 3 =
VAR Dates = SELECTCOLUMNS(FILTER(Dim_Time,Dim_Time[FiscalMonthName] = "May, 2018"),"Date",[CalendarDate])
RETURN
CALCULATE (
SUM ( Fact[Revenue]),
DATESBETWEEN (
Dates,
FIRSTDATE ( DATEADD (Dim_Time[CalendarDate] , -12, MONTH ) ),
IF (
MONTH ( MIN (Dim_Time[CalendarDate] ) ) = MONTH ( TODAY () ),
EDATE ( TODAY (), -12 ),
LASTDATE ( DATEADD ( Dim_Time[CalendarDate], -12, MONTH ) )
)
)
)
This one doesn't work at all. It says that DatesBetween will only accept a date reference column. So I tried my luck with this one
May PY 2 = CALCULATE(SUM(Fact[Revenue]),SAMEPERIODLASTYEAR(DATESMTD(Dim_Time[CalendarDate])))
This measure gives out the full month results instead of the MTD value. From my understanding, it should consider only the MTD dates for the current month and then give the prior year dates because of the SAMEPERIODLASTYEAR function, isn't it ?
