Power Bi Desktop : Dax formula having 3 or more filters (month, Year, Location, Category)

[Anonymous]

Dear Community,

I am new to Power Bi. 

Require your help and advice for creating a running / cumulative dax formula consisting of below criterias / filter : 

1) Year i.e. FY 2020 or 2019 or ...

2) Month : Jan / feb/ Mar...

3) Location : NY / Tex/ Hous/ Lousi..

4) Category : E / Y/ S/ T...

Thanks in advance,

[Anonymous]

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thanks Darlove for guidance.

Dear Guru/s,

So to rephrase my request : 

I have to calculate YTD Avg. headcount basis below criteria  :

"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 criteria/s  - so that AVG HC changes basis selection :

"Month",

"Employee Type",

:Status",

"Unit"

Please advise.

Thanking in advance,

Pratz

[Anonymous]

Please read this:

https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-mon...

Best
D

[Anonymous]

Thanks darlove for the link. On same lines have created a YTD running calculation but still nos. does not move with the slicers . Have been trying to build a report based on slicer selection of Month / Location/ Status/ & Type. Able to create MTD avg and %ages but slicers do not work in accordance to moving avg YTD HC or Total YTD exits - PBIX link below : https://drive.google.com/open?id=1_0PStuoPyc-L_fJGRxDBheQ7lSts7L9B This is a single table mentioned below : Date Zone Entity_Desc Ids Type Status Unit Status Exits DateKey YearNumber Year MonthNumber Month Apr-19 NCR-1 Discovery Network XX01 Employee Full Time LOS Opening 20190401 2020 FY 2020 1 April Apr-19 NCR-1 Walmart XX02 Temporary Part Time HOU Opening 20190401 2020 FY 2020 1 April Apr-19 NCR-1 Holiday Inn XX03 Contractual Full Time HOU Opening Inactive 20190401 2020 FY 2020 1 April May-19 NCR-1 Discovery Network XX01 Employee Full Time LOS Opening 20190501 2020 FY 2020 2 May May-19 NCR-1 Walmart XX02 Temporary Part Time HOU Opening Inactive 20190501 2020 FY 2020 2 May May-19 NCR-1 Holiday Inn XX04 Employee Nursing TEX New joiner 20190501 2020 FY 2020 2 May Jun-19 NCR-1 Discovery Network XX01 Employee Full Time LOS Opening 20190601 2020 FY 2020 3 June Jun-19 NCR-1 Walmart XX04 Temporary Part Time TEX Opening Inactive 20190601 2020 FY 2020 3 June Jun-19 NCR-1 Holiday Inn XX05 Contractual Full Time HOU New joiner Inactive 20190601 2020 FY 2020 3 June Jul-19 NCR-1 Discovery Network XX01 Employee Nursing LOS Opening 20190701 2020 FY 2020 4 July Jul-19 NCR-1 Walmart XX06 Contractual Full Time TEX New joiner 20190701 2020 FY 2020 4 July Please suggest on how to create a running YTD calculation that changes with slicer/ s selection. Regards, Pratz

[Anonymous]

You have to learn about star-schemas and then time-intel to write correct DAX. Start with the links to YT vids and these articles:

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-mon...

Best
D

[Anonymous]

If you have a single table in the model, then... your model is incorrect. Please check this out to know how to build correct models where things are simple to program:

https://www.youtube.com/watch?v=78d6mwR8GtA
https://www.youtube.com/watch?v=_quTwyvDfG0

I'll have a look at the file as well...

Best
D