Calculating Previous Year

[sp3jossy]

I have a table that shows average bed_density and Survey_year (2012 - 2020).

The table is in the format

bed_density | survey_year

  2                      |  2018

  2 .5                  |  2019  

 Please  I need help finding the previous Year bed_density so that I can analyze the variance. Can you suggest a DAX to use please?

 I created a measure using  a new column -
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.

[amitchandak · Accepted solution]

@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

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

[amitchandak · Accepted solution]

@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

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

[sp3jossy · Accepted solution]

Thank you !

[Ashish_Mathur]

Hi,

Try this approach

1. 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
2. Create a Calendar Table with this calculated Table formula: Calendar = Calendar(min(Data[Date]),max(Data[Date]))
3. In the Calendar Table, write this calculated column formula to extract the Year: Year = Year(Calendar[Date])
4. Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table
5. To your visual, drag the Year column from the Calendar Table
6. 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.

[v-qiuyu-msft]

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