cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JK_PowerBINew
Helper I
Helper I

% Change in Last 12 Months

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.
1 REPLY 1
amitchandak
Super User
Super User

@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))

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors