Forum Discussion
Calculating Previous Year
Year relative as Year_Relative = survey_year - YEAR(TODAY()) .Then, use year relative value to calculate bed density values:
PY_Bed density = Calculate ( [Bed_density], TableName[Year_Relative] = -1) but kept getting error in blank every time I switch year on the Year slicer.
sp3jossy , create a separate date/year table and use a formula like this. Join this table with your table
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))Refer to my Webinar
Thank you !
4 Replies
v-qiuyu-msftCommunity Support
Hi sp3jossy,
You can create a measure below:
PreYearDensity = CALCULATE(SUM('Table'[bed_density]),FILTER(ALL('Table'),'Table'[survey_year]=MAX('Table'[survey_year])-1))Best Regards,
Qiuyun Yu- Ashish_Mathur
Super User
Hi,
Try this approach
- In the table, write a calculated column formula to create a Date column: Date = 1*("1/1/"&Data[Year]). Format this column as a Date column
- Create a Calendar Table with this calculated Table formula: Calendar = Calendar(min(Data[Date]),max(Data[Date]))
- In the Calendar Table, write this calculated column formula to extract the Year: Year = Year(Calendar[Date])
- Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table
- To your visual, drag the Year column from the Calendar Table
- Write these measures
Bed Density = SUM(Data[bed_density])
Bed density in previous year = Calculate([Bed dinsity],previousyear(Calendar[Date]))
Variance in bed density = IFERROR([Bed Density]/[Bed density in previous year]-1,Blank())
Hope this helps.
- amitchandak
sp3jossy , create a separate date/year table and use a formula like this. Join this table with your table
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))Refer to my Webinar
Thank you !
