Forum Discussion
Helper IIHow to calculate latest estimate using DAX
Hi All, I need help in putting a correct DAX on calculating latest estimate of a frequency rate.
Here's the formula I crafted in EXCEL.
Formula in EXCEL
Thanks in advance!
Hi, PHEstaciMa1
Try formulas as below:
calculated column:
Manhours2 = VAR value1 = AVERAGEX ( FILTER ( 'Table', 'Table'[Manhours] <> BLANK () ), 'Table'[Manhours] ) RETURN IF ( ISBLANK ( 'Table'[Manhours] ), value1, 'Table'[Manhours] )measure:
M_Frequency Rate = DIVIDE ( SUM ( 'Table'[Count of Incidents] ) * 1000000, SUM ( 'Table'[Manhours2] ) )Best Regards,
Community Support Team _ Eason
5 Replies
- v-easonf-msft
Community Support
Hi, PHEstaciMa1
Try formulas as below:
calculated column:
Manhours2 = VAR value1 = AVERAGEX ( FILTER ( 'Table', 'Table'[Manhours] <> BLANK () ), 'Table'[Manhours] ) RETURN IF ( ISBLANK ( 'Table'[Manhours] ), value1, 'Table'[Manhours] )measure:
M_Frequency Rate = DIVIDE ( SUM ( 'Table'[Count of Incidents] ) * 1000000, SUM ( 'Table'[Manhours2] ) )Best Regards,
Community Support Team _ Eason
PHEstaciMa1Hi v-easonf-msft I tried your but somehow the remaining months of 2021 which has no data does not appear in my table after I unpivot the column. You may check the file I uploaded here in this link --> How to calculate latest estimate using DAX
- v-easonf-msft
Hi, PHEstaciMa1
Not fully sure what you mean.
Please check the table visual I created.
Best Regards,
Community Support Team _ Eason
- PHEstaciMa1
Hi v-easonf-msft adding the creation of calendar table and the initial solution you provided, it solves my issue. thank you very much!