% Change in Last 12 Months

[JK_PowerBINew]

Hi there,

I am trying to work out the % change in absence from May 2021 to May 2022 over that 12 month period. I have got answers but are not confident in their correctness and are looking for some validation if there is a better way to go around it. I am new to DAX and stats in Power BI!

I have created the following measures:

Total Absences 2021 = CALCULATE(COUNTA('May Annual Data'[Start Date of Absence]), FILTER('May Annual Data', 'May Annual Data'[Year] = 2021))

Total Absences 2022 = CALCULATE(COUNTA('May Annual Data'[Start Date of Absence]), FILTER('May Annual Data', 'May Annual Data'[Year] = 2022))

2022 % Diff TD = DIVIDE(([Total Absences 2022]- [Total Absences 2021]), [Total Absences 2021])

As the trend runs from May 2021 to May 2022, I fear the result may be inaccurate as 2021 will contain more months. I should say that there is no count of absence column for example, there is no Month | Year | Number of Absences that month year | meaning I can't use SUM. Should I create a table in this format?

Any thoughts? Thanks.

[amitchandak]

@JK_PowerBINew , try measures like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-12) ,-12,MONTH))

or

Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

Rolling 12 before 12 Sales =
var _max1 = maxx(allselcted(date),date[date]) // or today()
var _max = date(year(_max1), month(_max1)-12,1)
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

or

Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))

Rolling 12 before 12 Sales =
var _max1 = maxx(allselcted(date),date[date]) // or today()
var _max = date(year(_max1), month(_max1)-12,1)
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))