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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Zalina
Advocate I
Advocate I

Average by Category quick measure - last year condition

I am using Average by Category quick measure, which does exactly what I need (average of incident count by month).
However I need it to take into account only last year and not the whole dataset.
How do I adjust the DAX code of this quick measure to incorporate this condition?

Here is the DAX code from the quick measure:

Count of Number average per MonthYear =
AVERAGEX(
    KEEPFILTERS(VALUES('Calendar'[MonthYear])),
    CALCULATE(COUNTA('Created_INC'[Number]))
)


I am a newbie, so the more details the better 🙂
Thank you!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Zalina , with help from date table try like

 

Last YTD = CALCULATE([Avergae By Category],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Last year= CALCULATE([Avergae By Category],DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

Last Year = CALCULATE([Avergae By Category],previousyear('Date'[Date],"12/31"))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Zalina , with help from date table try like

 

Last YTD = CALCULATE([Avergae By Category],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Last year= CALCULATE([Avergae By Category],DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

Last Year = CALCULATE([Avergae By Category],previousyear('Date'[Date],"12/31"))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak 
Thank you for your help!
The second option worked for me.
I created a new measure and here is the code I used:

Last Year = CALCULATE([Average by Category],DATESYTD(ENDOFYEAR(dateadd('Calendar'[Calendar_Date],-1,Year)),"12/31"))


It's not exactly the solution for adjusting the quick measure and having one measure for the whole thing, but this serves my purpose.
Much appreciated!
🙂

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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