Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JK_PowerBINew
Helper II
Helper II

% 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors