Report output basis 3 or more drop down list

[Anonymous]

Dear Guru/s,

I have to calculate YTD Avg. headcount basis below dropdowns (All select /or any combination):

"Month",

"Employee Type",

:Status",

"Unit"

Table format is as follows for BI desktop : 

Filter 1 (Employee Type)

Filter 2 (Status)

Filter 3 (Unit)

FY_Year	Month	Closing HC	YTD Avg HC
FY2020	Apr	100	100
FY2020	May	95	195
FY2020	Jun	100	295

Have used running avg basis Year and Month  : 

Formula : 

YTD Avg HC = CALCULATE([MTD Avg. HC],

FILTER(

ALL(Attrition_DataTable3),

Attrition_DataTable3[Year] = MAX (Attrition_DataTable3[Year])

&& Attrition_DataTable3[DateKey] <= MAX(Attrition_DataTable3[DateKey])

)

)

But unable to modify the calculation basis given drop downs  - so that AVG HC changes basis selection :

"Month",

"Employee Type",

:Status",

"Unit"

Please advise.

Thanking in advance,

Pratz

[v-xuding-msft]

Hi @Anonymous ,

Are they from the same table? If not, what the relationships between them? It is better to share a dummy pbix file than describe. We will understand the situation clearly.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Xue Ding,

Yes, they are from the same table.

As advised attaching file link for the "Sample File -dummy" pbix with calculations (measures) and re...

So, if you go through the table, MTD calculation works in accordance to Slicers , but while calculat...

Please advice what is the best way to calculate YTD in accordance to the slicers.

Sample File - Dummy 

Regards,