How to calculate latest estimate using DAX

[PHEstaciMa1]

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!

[v-easonf-msft · Accepted solution]

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

[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!

[PHEstaciMa1]

Hi @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]

I see that you have create a calendar table for a period of 2018 to end of 2021, I guess that will be fine. Let me try it again and let you know ASAP.