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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JULY1
Helper I
Helper I

DAX for Average the result of the measure by month

Hi

 

I would like to average the result from one of my measure (Column B).

 

JULY1_0-1684406246812.png

According to the above picture, column B is the result from this measure

 

HC-Resign

 

HC = CALCULATE(COUNT('HC'[Employee ID]),'Attrition'[PE]="1")

Resign = CALCULATE([Resign], filter(ALL('Data as of_LookUp'[Data as of]),'Data as of_LookUp'[Data as of]<MAX('Data as of_LookUp'[Data as of])))
 
And after I got the column B, I tried to average them to result in column D, using ALL / FILTER anything that I can think of but it doesn't work.
 
So anyone please give me the light.
 
Thank youu!
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @JULY1 

please try

Average =
VAR CurrentDate =
MAX ( 'Data as of_LookUp'[Data as of] )
VAR SelectedMonths =
CALCULATETABLE (
VALUES ( 'Data as of_LookUp'[YearMonth] ),
ALLSELECTED ( 'Data as of_LookUp' ),
'Data as of_LookUp'[Data as of] <= CurrentDate
)
RETURN
AVERAGEX (
SelectedMonths,
VAR CurrentMonth = 'Data as of_LookUp'[YearMonth]
RETURN
CALCULATE (
[HC],
'Data as of_LookUp'[YearMonth] = CurrentMonth,
ALLSELECTED ( 'Data as of_LookUp' )
)
)

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @JULY1 

please try

Average =
VAR CurrentDate =
MAX ( 'Data as of_LookUp'[Data as of] )
VAR SelectedMonths =
CALCULATETABLE (
VALUES ( 'Data as of_LookUp'[YearMonth] ),
ALLSELECTED ( 'Data as of_LookUp' ),
'Data as of_LookUp'[Data as of] <= CurrentDate
)
RETURN
AVERAGEX (
SelectedMonths,
VAR CurrentMonth = 'Data as of_LookUp'[YearMonth]
RETURN
CALCULATE (
[HC],
'Data as of_LookUp'[YearMonth] = CurrentMonth,
ALLSELECTED ( 'Data as of_LookUp' )
)
)

Hi @tamerj1 

 

Sorry to bother you again.

It worked at first but after I played around with something else and came back, I found that the result has changed and it's not correct anymore 😢 
Could you help me figure this out again.

 

The result column was from the DAX you provided earlier, it looks like it was divided by 1 in January, 1.99 in February, 2.98 in March, while it should be divided by 1 2 3 respectively in order to calculate the average.

JULY1_0-1688102215330.png

 

Thank you.

@JULY1 
It is difficult to tell without working on the actual report.

Hi @tamerj1 ,

 

It worked.

Thank you so much. 🙏🙏🙏

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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