topic Re: Get Remaining Working days for each day in month in DAX Commands and Tips

Get Remaining Working days for each day in month

Anonymous — Fri, 30 Aug 2019 11:01:26 GMT

Hi,

I am trying to calculate the Working days remaining for each month,

I have created a column to calculate if each day is Working day or not, But my requirement is if it is weekend or a non working day then it should show the previous days remaining working days

I have created the Rank for Working Day in Month by using the following DAX

Rank For Working Day in Month =

// Calculates the Working Day Rank by YearMonth

VAR AllDates = Dates[Date]

VAR YearMon = Dates[YearMon]

RETURN

CALCULATE (

RANK.EQ ( AllDates, Dates[Date], DESC ),

FILTER ( ALL( Dates ), AND(Dates[WorkingDay] = "Y", Dates[YearMon] = YearMon)

))

Date	WorkingDay	Rank For Working Day in Month
Thursday, 1 August 2019	Y	21
Friday, 2 August 2019	Y	20
Saturday, 3 August 2019	N
Sunday, 4 August 2019	N
Monday, 5 August 2019	Y	19
Tuesday, 6 August 2019	Y	18
Wednesday, 7 August 2019	Y	17
Thursday, 8 August 2019	Y	16
Friday, 9 August 2019	Y	15
Saturday, 10 August 2019	N
Sunday, 11 August 2019	N
Monday, 12 August 2019	Y	14
Tuesday, 13 August 2019	Y	13
Wednesday, 14 August 2019	Y	12
Thursday, 15 August 2019	Y	11
Friday, 16 August 2019	Y	10
Saturday, 17 August 2019	N
Sunday, 18 August 2019	N
Monday, 19 August 2019	Y	9
Tuesday, 20 August 2019	Y	8
Wednesday, 21 August 2019	Y	7
Thursday, 22 August 2019	Y	6
Friday, 23 August 2019	Y	5
Saturday, 24 August 2019	N
Sunday, 25 August 2019	N
Monday, 26 August 2019	N
Tuesday, 27 August 2019	Y	4
Wednesday, 28 August 2019	Y	3
Thursday, 29 August 2019	Y	2
Friday, 30 August 2019	Y	1
Saturday, 31 August 2019	N

This is what I have achieved so for but, as you can see there are blanks for example on the 3rd and 4th I need to get 20.

Could anybody please help me in this regard.

Thanks,

Prime

Re: Get Remaining Working days for each day in month

Anonymous — Fri, 30 Aug 2019 12:15:40 GMT

DAX is not a tool for this. Use Power Query for this since it's where such calculations belong.

Best
D.

Re: Get Remaining Working days for each day in month

Anonymous — Fri, 30 Aug 2019 12:18:58 GMT

Thanks for your suggestion.

But unfortunately this is a date table and created using the CALENDERAUTO() as shown in below syntax

Dates =

ADDCOLUMNS (

CALENDARAUTO(),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Date 2", FORMAT([Date], "ddd dd/mm/yyyy"),

"Year", YEAR ( [Date] ),

"Monthnumber", MONTH([Date]),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"Month", MONTH([DATE]),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),

"YearMon", FORMAT ( [Date], "YYYY" ) & FORMAT ( [Date], "mmm" ),

"WeekNo", FORMAT([Date], "ww"),

"DayNo", FORMAT([Date], "d"),

"MonthLongYear", FORMAT ( [Date], "mmmm" ) & " " & FORMAT([Date], "YYYY"),

"DayNoMonthShort", FORMAT([Date], "ddd") & " " & FORMAT([Date], "d"),

"DayNoINT", DAY([Date]),

"DDMMYYYY", FORMAT([Date], "DD/MM/YYYY")

)

Re: Get Remaining Working days for each day in month

Anonymous — Fri, 30 Aug 2019 12:20:38 GMT

Then create a proper Date table in PQ. If you don't know how, you'll find it via Google.

That's really easier than you think.

Best
D.

Re: Get Remaining Working days for each day in month

Anonymous — Fri, 30 Aug 2019 12:33:06 GMT

I have created a date table in PQ, just wondering how do I account bank holidays into PQ?

Re: Get Remaining Working days for each day in month

Anonymous — Fri, 30 Aug 2019 14:47:30 GMT

Create a table of bank holidays using data found on the web and then left outer join the Date table to the Bank Holidays table. This is also easy.

Best
D.

Re: Get Remaining Working days for each day in month

Anonymous — Fri, 30 Aug 2019 16:30:03 GMT

I have created 2 Calculated columns in Date table

Rank For Working Day in Month =

// Calculates the Working Day Rank by YearMonth

VAR AllDates = Dates[Date]

VAR YearMon = Dates[YearMon]

RETURN

CALCULATE (

RANK.EQ ( AllDates, Dates[Date], DESC ),

FILTER ( ALL( Dates ), AND(Dates[WorkingDay] = "Y", Dates[YearMon] = YearMon)

))

and

Rank For NON Working Day in Month =

// Calculates the Working Day Rank by YearMonth

VAR AllDates = Dates[Date]

VAR YearMon = Dates[YearMon]

RETURN

CALCULATE (

RANK.EQ ( AllDates, Dates[Date], DESC ),

FILTER ( ALL( Dates ), AND(Dates[WorkingDay] = "N", Dates[YearMon] = YearMon)

))

which gives the raking for working days and NON working days in a month in 2 column. Note YearMon is the concatination of Year and Month

Now the finale Measure is

No Of Working Days Reamining =

VAR NoOfDaysInMonth = DAY(

IF(

MONTH(MAX(Dates[Date])) = 12,

DATE(YEAR(MAX(Dates[Date])) + 1,1,1),

DATE(YEAR(MAX(Dates[Date])), MONTH(MAX(Dates[Date])) + 1, 1)

) - 1

)

VAR CalculationForNonWorkingDays = NoOfDaysInMonth- MAX(Dates[Rank For NON Working Day in Month]) - MAX(Dates[DayNo]) + 1

Return

IF(MAX(Dates[Rank For Working Day in Month]) = BLANK(),CalculationForNonWorkingDays,MAX(Dates[Rank For Working Day in Month]))

Regards,

Prime