Working Days based on Current Fiscal Month

DAX DAX Calculations
1

Hi ,
I am trying to get the total sum of Working days for the selected Fiscal Month. For Current Month, it gives 7 for Nov as Period 11 Starts on Oct 25th

WorkingdaysNov
WorkingdaysNov776×269 8.4 KB

WorkdaysForPeriod = 

CALCULATE(COUNT(date_dimension[Date]),FILTER(ALLSELECTED(date_dimension),date_dimension[OperationPeriod] = SELECTEDVALUE(date_dimension[OperationPeriod]) && date_dimension[IsWeekend] = "0" && date_dimension[IsHoliday] = "0"))

Whenever I selected previous completed Months I do get correct number but I need to get the current month value to be 21 however I cant get it to my desired result

WorkingdaysNov1
WorkingdaysNov1833×720 85.8 KB

Please guide or Help me

Thanks
Archer

2

@Archer
If the formula was working correctly previously then i guess that something else must have gone wrong or changed in your dataset. It would be helpful if you can share your PBIX and excel file to look at.

Thanks

3

Sorry I am trying to develop this logic for the first time … I meant when I select previous month lets say Oct, Sep then I am getting correct Count of Working Days

Thanks
Archer

4

@Archer
I have simplified the formula and it’s correctly pulling out working days based on month or period:

WorkDaysInPeriod =
VAR SelectedMonth = SELECTEDVALUE( ‘Date’[MonthOfYear] )
VAR Result =
CALCULATE(
COUNT( ‘Date’[IsWorkingDay] ),
FILTER(
ALL( ‘Date’ ),
‘Date’[MonthOfYear] = SelectedMonth ) )
Return
Result

1
11920×1080 262 KB

I have no idea how you have set up your WorkDays but I would suggest to have WorkDays (excluding Weekends and Holidays) in Power Query rather than making it complex in DAX.
PBIX file is attached.
Working Days.pbix (111.4 KB)

5

Thanks for posting your question @Archer. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

6

Hi @MudassirAli, Thank you for your help! Much appreciated

My Storage mode is Direct Query and I have a date_dimension with IsWeekend and IsHoliday flags already built into this table.
My initial DAX worked and I figured this was data model issue where I had bi-directional on with another fact table that was filtering out current months’ date to only till Nov 3rd. I have changed the relationship and ended up with two versions of working DAX.

WorkdaysForPeriod =

CALCULATE(
COUNT
(date_dimension[Date]),
FILTER(
ALLSELECTED(date_dimension),
date_dimension[OperationPeriod] = SELECTEDVALUE(date_dimension[OperationPeriod]) && date_dimension[IsWeekend] = "0" && date_dimension[IsHoliday] = "0")
)

Workdays =
VAR _WorkCount =
CALCULATE (
COUNTX
(FILTER
(date_dimension, date_dimension[Weekday] <> “1” && date_dimension[Weekday] <> “7” && date_dimension[IsHoliday] = “0” ),
date_dimension[Weekday])
)
RETURN
_WeekdayCount

I will use your Code as well :slight_smile:

Thanks
Archer