Regarding the non-working days on/off toggle in the settings

[SusumuYamagami]

There’s an on/off toggle for non-working days in the settings, and as far as I understand, enabling this treats Saturdays and Sundays as non-working days.
Using the start date and duration, I’m recalculating the duration by referring to my own factory non-working days table with a function.
Is there a way to improve this to get the same result without relying on a function?

[Aala_Ali · Accepted solution]

Hi @SusumuYamagami ,

I hope you are doing Well..

From what I’ve seen, this behavior is usually not caused by the toggle itself, but more by how the model is set up behind the scenes.

The “non-working days” toggle depends on having a proper Date table and clear working-day logic. If those aren’t defined or used in your calculations, the toggle might not show any visible effect.

You can check the following:

1. Date table
Make sure you’re using a dedicated Date table and that it’s marked as a Date table in your model.

2. Working day flag
Power BI doesn’t automatically know weekends or non-working days, so you need to define it, for example:

IsWorkingDay = IF(WEEKDAY('Date'[Date],2) <= 5, TRUE(), FALSE())

3. Measures
If your measures ignore filters (like using ALL or REMOVEFILTERS), the toggle won’t have any effect.
Try testing with a simple measure like:

Working Days Revenue =
CALCULATE(
SUM(Fact[Revenue]),
'Date'[IsWorkingDay] = TRUE()
)

4. Relationships
Ensure there’s an active relationship between your Date table and your fact table, otherwise the filtering won’t work properly.

Also, this behavior is somewhat similar to other toggle-related issues in Power BI where the UI doesn’t fully sync with the model immediately. For example, this thread might be helpful:
https://community.fabric.microsoft.com/t5/Service/Analyze-in-Excel-grayed-out-until-Action-Bar-is-to...

Hope this helps! 😊
If it solves your issue, please consider marking it as a solution and giving kudos so others can benefit too.

Alaa Ali
User Group Leader – Sudan Microsoft Fabric Community
LinkedIn: https://www.linkedin.com/in/aala-ali/

[v-dineshya · Accepted solution]

Hi @SusumuYamagami ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @Aala_Ali , Thank you for your prompt response.

Hi @SusumuYamagami , You can use Non-Working Days toggle by switching to a calendar-table driven calculation instead of recalculating duration row-by-row.

Please refer below steps.

1. Create a Calendar Table with Working Day Flag.

Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2024,1,1), DATE(2030,12,31)),
"IsWeekend", WEEKDAY([Date],2) > 5,
"IsWorkingDay", IF(WEEKDAY([Date],2) > 5, 0, 1)
)

2. Calculate End Date (Skip Non-Working Days).

End Date =
VAR StartDate = Tasks[Start Date]
VAR Duration = Tasks[Duration]

RETURN
MAXX (
TOPN (
Duration,
FILTER (
Calendar,
Calendar[Date] >= StartDate &&
Calendar[IsWorkingDay] = 1
),
Calendar[Date]
),
Calendar[Date]
)

3. If your toggle is a parameter table, then use below DAX code in the calculation instead of Calendar[IsWorkingDay].

IsWorkingDay Effective =
IF (
SELECTEDVALUE(Settings[NonWorkingDaysToggle]) = 1,
Calendar[IsWorkingDay],
1
)

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya · Accepted solution]

Hi @SusumuYamagami ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @Aala_Ali , Thank you for your prompt response.

Hi @SusumuYamagami , You can use Non-Working Days toggle by switching to a calendar-table driven calculation instead of recalculating duration row-by-row.

Please refer below steps.

1. Create a Calendar Table with Working Day Flag.

Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2024,1,1), DATE(2030,12,31)),
"IsWeekend", WEEKDAY([Date],2) > 5,
"IsWorkingDay", IF(WEEKDAY([Date],2) > 5, 0, 1)
)

2. Calculate End Date (Skip Non-Working Days).

End Date =
VAR StartDate = Tasks[Start Date]
VAR Duration = Tasks[Duration]

RETURN
MAXX (
TOPN (
Duration,
FILTER (
Calendar,
Calendar[Date] >= StartDate &&
Calendar[IsWorkingDay] = 1
),
Calendar[Date]
),
Calendar[Date]
)

3. If your toggle is a parameter table, then use below DAX code in the calculation instead of Calendar[IsWorkingDay].

IsWorkingDay Effective =
IF (
SELECTEDVALUE(Settings[NonWorkingDaysToggle]) = 1,
Calendar[IsWorkingDay],
1
)

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[SusumuYamagami · Accepted solution]

Based on your advice, I was able to create a DAX formula that derives the correct End Date from Duration by using IsWorkingDay. Thank you very much!

What I tested is described below.
When January 19, 2026 is set as a factory holiday, and Start Date = January 16, 2026 with Duration = 2, the End Date should be January 21, 2026 rather than January 20, 2026.
By modifying the DAX formula as shown below, the End Date was calculated correctly.

End Date =

VAR StartDate = Tasks[Start Date]

VAR Duration  = Tasks[Duration]

VAR WorkingDaysAfterStart =

    FILTER (

        CalendarTable,

        CalendarTable[Date] > StartDate

            && CalendarTable[IsWorkingDay] = 1

    )

RETURN

IF (

    ISBLANK ( StartDate ) || ISBLANK ( Duration ),

    BLANK (),

    MAXX (

        TOPN (

            Duration,

            WorkingDaysAfterStart,

            CalendarTable[Date],

            ASC

        ),

        CalendarTable[Date]

    )

)

 

[v-dineshya]

Hi @SusumuYamagami ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @SusumuYamagami ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Aala_Ali · Accepted solution]

Hi @SusumuYamagami ,

I hope you are doing Well..

From what I’ve seen, this behavior is usually not caused by the toggle itself, but more by how the model is set up behind the scenes.

The “non-working days” toggle depends on having a proper Date table and clear working-day logic. If those aren’t defined or used in your calculations, the toggle might not show any visible effect.

You can check the following:

1. Date table
Make sure you’re using a dedicated Date table and that it’s marked as a Date table in your model.

2. Working day flag
Power BI doesn’t automatically know weekends or non-working days, so you need to define it, for example:

IsWorkingDay = IF(WEEKDAY('Date'[Date],2) <= 5, TRUE(), FALSE())

3. Measures
If your measures ignore filters (like using ALL or REMOVEFILTERS), the toggle won’t have any effect.
Try testing with a simple measure like:

Working Days Revenue =
CALCULATE(
SUM(Fact[Revenue]),
'Date'[IsWorkingDay] = TRUE()
)

4. Relationships
Ensure there’s an active relationship between your Date table and your fact table, otherwise the filtering won’t work properly.

Also, this behavior is somewhat similar to other toggle-related issues in Power BI where the UI doesn’t fully sync with the model immediately. For example, this thread might be helpful:
https://community.fabric.microsoft.com/t5/Service/Analyze-in-Excel-grayed-out-until-Action-Bar-is-to...

Hope this helps! 😊
If it solves your issue, please consider marking it as a solution and giving kudos so others can benefit too.

Alaa Ali
User Group Leader – Sudan Microsoft Fabric Community
LinkedIn: https://www.linkedin.com/in/aala-ali/