Power BI Calculate Working Days For Every Month Dynamically

[Preeti123]

Hi Community Members,

Since I am new to Power BI, I don't know much about it, though I am learning. I have a simple requirement where I need to use Power BI DAX to display the current working day number out of the overall working day number and that should appear dynamically every month.

For example, 2020 April month has a total of 22 working days. And suppose today is the 20th working day out of 22days. So in Power BI Report, I need to display it as 20 of 22 days. Furthermore, I need to display "As of April 28, 2020" above it (Using DAX only and this one should likewise alter dynamically). Refer to the screenshot link below:

Power-BI-Calculate-Working-Days-Dynamically 

Can anyone please help me out to achieve this? Thanks in Advance!!

[PaulOlding]

Hi @Preeti123 

You could use the NETWORKDAYS function to do this.

Work Day = 
VAR _Today = TODAY()
VAR _WorkDaysInMonth = NETWORKDAYS (EOMONTH (_Today, -1) + 1, EOMONTH (_Today, 0))
VAR _WorkDayToday = NETWORKDAYS (EOMONTH (_Today, -1) + 1, _Today)
VAR _Result = _WorkDayToday & " of " & _WorkDaysInMonth & " days"
RETURN
    _Result

As Of Date = 
VAR _Today = TODAY()
VAR _Result = "As of " & FORMAT(_Today, "mmmm d, yyyy")
RETURN
    _Result

Note this uses the TODAY() function to get today's date.  One extension yuou might want to do is base it on the last time the data was refreshed, rather than assume it's updated successfully today.  You can use M function DateTime.LocalNow() in a new query in Power Query to get that.