Forum Discussion
Post PatronSame period last year using 1 table
Hi everyone,
I would like to get the value same period last year using one table. I have my date and measure (value) in my table. I have no date dimension. I have this formula but it is not working 😞
Can anyone help me?
Previous Year Rainfall =
VAR SelectedDate = MAX(RainfallData[Date])
VAR PreviousYearDate = DATEADD(SelectedDate, -1, YEAR)
RETURN
CALCULATE(
SUM(RainfallData[Rainfall]),
FILTER(
ALL(RainfallData),
RainfallData[Date] = PreviousYearDate
)
)
Thank you!
Hi icdns ,
According to your statement, I think you can try code as below to create a measure.
Previous Year Rainfall = VAR _CURDATE = MAX(RainfallData[Date]) RETURN CALCULATE( SUM(RainfallData[Rainfall]), FILTER( ALLSELECTED(RainfallData), RainfallData[Date] = DATE(YEAR(_CURDATE)-1,MONTH(_CURDATE),DAY(_CURDATE)) ) )Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- littlemojopuppy
Community Champion
icdns why don't you have a date table? This would be really, really easy if you had a date table and marked it appropriately. Then you could use time intelligence functions and specifically SAMEPERIODLASTYEAR().
icdnsIs it possible if I will not use a date table?
Hi icdns ,
According to your statement, I think you can try code as below to create a measure.
Previous Year Rainfall = VAR _CURDATE = MAX(RainfallData[Date]) RETURN CALCULATE( SUM(RainfallData[Rainfall]), FILTER( ALLSELECTED(RainfallData), RainfallData[Date] = DATE(YEAR(_CURDATE)-1,MONTH(_CURDATE),DAY(_CURDATE)) ) )Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
