Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mdrammeh
Helper III
Helper III

How to calculate number workday in Power Query and Power BI

I have a list of tasks in my database with different start dates and required finish dates organized in two columns. For each column, I want to create a new column to capture the "MAX", "MIN", "Average" date by Task ID for scheduled dates after the current year. Keep in mind that the dataset has millions of records that date back to 10 years but I want to create a Gantt Chart to capture the "WORKDAY" duration for the current year only. WORKDAY-Gantt.PNG

4 REPLIES 4
v-huizhn-msft
Employee
Employee

Hi @mdrammeh,

Please create a calendar table, create a flag column if the date is workday. Then you can use count workdays.

You want to calculate the "WORKDAY" duration for the current year only, so use the following formula to create a calendar table. 

Calendar = 
CALENDAR (  DATE(2017,1,1), MAX ( FactTable[Finish date] ) )


Then add a flag column using the formula.

 

WorkingDay_Mark = 
VAR WeekDayNum =
    WEEKDAY ( Calendar[Date] )
RETURN
    (
        IF (
            OR (
                OR ( WeekDayNum = 1, WeekDayNum = 7 ),
                RELATED ( Holidays[Date] ) <> BLANK ()
            ),
            FALSE (),
            TRUE ()
        )
)

Finally, calculate the count of workdays.

WorkingDay_Num = 
COUNTROWS (
    FILTER (
       Calendar,
        AND (
            AND (
                Calendar[Date] >= FactTable[Start date],
                Calendar[Date] <= FactTable[End date]
            ),
            Calendar[WorkingDay_Mark]
        )
    )
)


If you have any other problem, please feel free to ask.


Best Regards,
Angelia



Hi Angela,

 

Could you share a little more detail about how you did this in Power Query? I have ben getting an error message for each time I try. I copied and pasted the formula into the Query Editor but it didn't work.

 

Thanks,

 

M

Hi @mdrammeh,

The solution above is create calculated column or measure in Power BI desktop. If you want to achieve this in Power Query, I personally recommand you post your thread Power Query forum. You will get the most qualified pool of respondents. Thanks for understanding.

Best Regards,
Angelia

I am trying to figure out how to calculate working days between dates power query by hour, day, month, and year in a single month. Also is the table field is blank for any of these fields, and want power query to return a blank cell for the durationDuration.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.