Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone,
This has GOT to be simple and I'm just overthinking it. I am trying to write an expression that returns which work day (non-weekend, non-holiday) a particular date is of a month. I have a Date column, a WorkDayCount column where a 1 represents a non-weekend day/non-holiday, and a 0 denotes a weekend day or holiday. I have a FirstDayofMonth column and a LastDayofMonth column. I want to create a fifth column that indicates the work day of that particular month by performing a cumulative sum of WorkDayCount between the end points of FirstDayofMonth and LastDayofMonth.
Any help is much appreciated!
Solved! Go to Solution.
Correct.
After rooting around in the forum, I was able to answer my own question using COUNTROWS filtering on those rows I identified as a non-weekend day/non-holiday with a 1
WorkDay =
if(
DateTable[WorkdayCount]=0,
blank(),
calculate(countrows(DateTable),
DATESBETWEEN(DateTable[Date],
STARTOFMONTH(DateTable[Date]),
DateTable[Date]),
DateTable[WorkdayCount]=1,
all(DateTable))
&"WD"
)
@rc8425 are you looking in date dimension on how many working days are in a month? CorrecT?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Correct.
After rooting around in the forum, I was able to answer my own question using COUNTROWS filtering on those rows I identified as a non-weekend day/non-holiday with a 1
WorkDay =
if(
DateTable[WorkdayCount]=0,
blank(),
calculate(countrows(DateTable),
DATESBETWEEN(DateTable[Date],
STARTOFMONTH(DateTable[Date]),
DateTable[Date]),
DateTable[WorkdayCount]=1,
all(DateTable))
&"WD"
)
@rc8425 ok then you can add new column with following DAX, change column and table name as per your data model
Month Work Day = VAR __firstDate = CALCULATE( MAX( Workday[First] ) ) VAR __lastDaste = CALCULATE( MAX( Workday[Last] ) ) RETURN CALCULATE( SUM( Workday[Workday] ), Workday[First] >= __firstDate, Workday[Last] <= __lastDaste )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |