Cumulative value Based in other Measure

[guilherme_gq]

Hi!

I trying to calculate a Average Headcount (basically is the sum of Monthly HeadCount / number of months). The Monthly HeadCount is the measure that calculates a total value for the month.

for example:

                                      SET   OUT  NOV  DEZ    JAN
Monthly HeadCount    400   430   450    445    480
Average Headcount     400   415   427    431    441

• October is (430 + 400)/2.
• For November, is (450+430+400)/3.

does anyone have any idea how to calculate this measure?

Thanks!

[v-jiascu-msft · Accepted solution]

Hi @guilherme_gq,

I would suggest you create a date table if you don't have one. Please check out the demo in the attachment.

Measure =
CALCULATE (
    AVERAGEX (
        SUMMARIZE ( 'Table', 'Calendar'[MonthNum], "v", [Monthly HeadCount] ),
        [v]
    ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Date] <= MAX ( 'Table'[Date] )
    )
)

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @guilherme_gq,

Could you please mark the proper answer as a solution?

Best Regards,
Dale

[v-jiascu-msft · Accepted solution]

Hi @guilherme_gq,

I would suggest you create a date table if you don't have one. Please check out the demo in the attachment.

Measure =
CALCULATE (
    AVERAGEX (
        SUMMARIZE ( 'Table', 'Calendar'[MonthNum], "v", [Monthly HeadCount] ),
        [v]
    ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Date] <= MAX ( 'Table'[Date] )
    )
)

Best Regards,

Dale

[guilherme_gq]

Thanks, man!!!

that worked!!!