Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |