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
nhol
Advocate II
Advocate II

Number of working days

Hi,

 

I'm looking after counting working days function. However, not for historical months (like February in the example attached) but also for current month where my latest and greatest date in the system is up until today only (March 4th).

In other words I'm searching for a formula that will extract the month we are in based on MMMM and YYYY from my [insert_time] table and will tell me how many working days are expected to be in this month.

I can not add in date table as I'm sourcing SQL database via a DirectQuery mode. So I have some limitations there.

 

Thanks,

NH

 

CountingWorkingDays.png

1 ACCEPTED SOLUTION

@nhol- Try this, it takes a single input date (in the example, Calendar[Date]) and returns working days in the month.

 

NetWorkDays = 
VAR StartDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date])),1)
VAR EndDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date]))+1,1)-1
VAR Calendar1 = CALENDAR(StartDate,EndDate)
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

I have a Quick Measure in the Gallery for that. It adds a Calendar table dynamically.

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Zubair_Muhammad
Community Champion
Community Champion

Hi @nhol

 

Try this MEASURE

 

CountWorkingDays =
VAR Temptable =
    ADDCOLUMNS (
        VALUES ( TableName[insert_time] ),
        "WeekDay", WEEKDAY ( TableName[insert_time], 2 )
    )
RETURN
    COUNTROWS ( FILTER ( Temptable, [WeekDay] <= 5 ) )

Regards
Zubair

Please try my custom visuals

Hi,

 

This formula provide me with "1" for each row that has a stamp date. I need a formula that based on the date, assuming March 5th 2018, it will know that we are in the month of March (2018) and it will provide me with 22 working days eventhough I don't have a full month dates in teh system.

 

Thanks!

NH 

@nhol- Try this, it takes a single input date (in the example, Calendar[Date]) and returns working days in the month.

 

NetWorkDays = 
VAR StartDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date])),1)
VAR EndDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date]))+1,1)-1
VAR Calendar1 = CALENDAR(StartDate,EndDate)
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Brilliant!!!

Thank you so much!

 

Nir

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.