Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have attached a link to my pbix sample file (here - https://tinyurl.com/y522q5l7 )
I want to select a date range and retrieve the number of working days in each of those represented months. So, if the date slicer were 8/1/20-8/15/20 that is the month of August. The month of August has 21 working days in it (for my specific calendar anyway). Therefore, I need the result of "21".
If the date slicer is 8/1/20-9/15/20 that is August and September. August has 21 working days and September has 21 working days in my calendar file so I need to have a result of "42".
In my DateDim I have whether or not the date is a working day.
I am having trouble getting the DAX right to give me that total working number of all the months and would be most appreciative if somebody could help me out.
Thanks!
Proud to be a Datanaut!
Private message me for consulting or training needs.
Solved! Go to Solution.
Hi @collinq -
This might not be that elegant, but give it a try:
NumWorkDays =
VAR __MinDt =
CALCULATE ( MIN ( DateTab[Date] ), ALLSELECTED ( DateTab[Date] ) )
VAR __MaxDt =
CALCULATE ( MAX ( DateTab[Date] ), ALLSELECTED ( DateTab[Date] ) )
VAR __MinMth =
MONTH ( __MinDt )
VAR __MinYr =
YEAR ( __MinDt )
VAR __MaxMth =
MONTH ( __MaxDt )
VAR __MaxYr =
YEAR ( __MaxDt )
VAR __MinDate =
DATE ( __MinYr, __MinMth, 1 )
VAR __MaxDate =
EOMONTH ( DATE ( __MaxYr, __MaxMth, 1 ), 0 )
RETURN
CALCULATE (
COUNTROWS (
FILTER (
ALL ( DateTab ),
DateTab[Date] >= __MinDate
&& DateTab[Date] <= __MaxDate
&& DateTab[IsWorkDay]
)
)
)
(IsWorkDay is a Boolean on the Date table marking Sat-Sun as false, so ignoring holidays)
Should be able to span years, not just months.
Hope this helps
David
Hi @collinq -
This might not be that elegant, but give it a try:
NumWorkDays =
VAR __MinDt =
CALCULATE ( MIN ( DateTab[Date] ), ALLSELECTED ( DateTab[Date] ) )
VAR __MaxDt =
CALCULATE ( MAX ( DateTab[Date] ), ALLSELECTED ( DateTab[Date] ) )
VAR __MinMth =
MONTH ( __MinDt )
VAR __MinYr =
YEAR ( __MinDt )
VAR __MaxMth =
MONTH ( __MaxDt )
VAR __MaxYr =
YEAR ( __MaxDt )
VAR __MinDate =
DATE ( __MinYr, __MinMth, 1 )
VAR __MaxDate =
EOMONTH ( DATE ( __MaxYr, __MaxMth, 1 ), 0 )
RETURN
CALCULATE (
COUNTROWS (
FILTER (
ALL ( DateTab ),
DateTab[Date] >= __MinDate
&& DateTab[Date] <= __MaxDate
&& DateTab[IsWorkDay]
)
)
)
(IsWorkDay is a Boolean on the Date table marking Sat-Sun as false, so ignoring holidays)
Should be able to span years, not just months.
Hope this helps
David
Hi @dedelman_clng ,
Elegant or not, that is beautiful to me! Thanks so much!!! I was definitely going down a much different and eventually ineffective solution. Thanks for your time and thanks for your effort!
Proud to be a Datanaut!
Private message me for consulting or training needs.