Weekday filtering in DAX

[baneworth]

Dear All,

after checking the forum for hours i give up, i couldn't find the solution to my problem.

If anyone could be so kind and take a look at my problem.

I have a Column with dates when employees booked their activity, i then created the total and avg from employees booked by day (see table botttom left for avg by month & bottom right by day)

I now have to repeat the same to get avg by month without weekends.

RFT = Employee (RFT is a measure)

Formula for average in table on left (this includes all days)

RFTavg = AVERAGEX(VALUES(vwPassdownNew[Work_Day]),[RFT])

What would be DAX excluding weekends?

Best regards

[AllisonKennedy]

@baneworth , do you have a DimDate table? If you have a good Dates table with a Weekday column, which specifies whether it's a weekend or not, then this is easy.

My date table (you'll need to add the Weekday column, can use @amitchandak 's solution: Week_Day = if(WEEKNUM([Date],2)>=6,0,1) to add the Weekday column.

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Radacad date table (I think it has weekday already from memory, but not sure):

https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query

Then do: 

RFTweekdayAvg =  AVERAGEX(DimDate[Week_Day]),[RFT])

Note I don't need the VALUES() function here because I'm using a Dim date table rather than the fact table: 

https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html

[amitchandak]

@baneworth , Try like

measure

RFTavg = calculate(AVERAGEX(VALUES(vwPassdownNew[Date]),[RFT]), filter(vwPassdownNew,vwPassdownNew [Work_Day] =1))

where column

Work_Day = if(WEEKNUM([Date],2)>=6,0,1)

[baneworth]

Dear @amitchandak 

Thank you for quick reply.

I have 2 Questions

1. What do you refer to as "vwPassdownNew[Date]"?  I do not have such as of now.

2. my Work_Day is a column created by SQL Datasource. Therefore i can not change as you recommended. What can i do instead? Or did you mean a new measure?

Best regards

Matthias