Hi,
I have been trying to display a number of working days in a month example below. I am able to calculate sales on working days be taking these steps but can’t figure out a way to display it.
First, Conditional formatting column for working days
= if [DayInWeek] = 6 then 2 else if [DayInWeek] = 0 then 2 else 1)
Second, Workdays (measure) = SUM('Date'[WorkingDays])
Days Total Sales
January- Working Day 1 $140
January- Working Day 2 $150
January- Working Day 3 $160
Feb- Working Day 1 $130
Feb-Working Day 2 $120
I would appreciate some help.
This is an interesting one.
Here’s how I solved it
First create the calculated column as you have I believe
Then use this formula for the sales on working days only
Then the hard part.
You need to create a formula that finds the working days only. To do this I used RANKX.
Here’s the formula
Workday Number =
VAR CurrentMonth = SELECTEDVALUE( Dates[Month & Year] )
VAR MonthTable = FILTER( ALL( Dates ), Dates[Working Days] = "Weekday" && Dates[Month & Year] = CurrentMonth )
RETURN
IF( SELECTEDVALUE( Dates[Working Days] ) <> "Weekday",
BLANK(),
RANKX( MonthTable, CALCULATE( AVERAGE( Dates[DayOfMonth] ) ), , ASC ))
And the results
1 Like
That is very clever!
I will give it a try and let you know… Thanks!
It doesn’t seem to work for me. Not sure what I am doing differently. Also, is there any way I can only display Workday number instead of dates?
Formula
Workday number =
Var
CurrentMonth = SELECTEDVALUE('Date'[Month & Year])
VAR
MonthTable = FILTER(ALL('Date'), 'Date'[Holidays & WorkDays] = "Work Day" && 'Date'[Month & Year] = CurrentMonth)
RETURN
IF (SELECTEDVALUE('Date'[Holidays & WorkDays]) <> "Work Day",
BLANK(),
RANKX(MonthTable, CALCULATE(AVERAGE('Date'[DayOfMonth])), , ASC))
It should work, works fine at my end.
Hard for me to tell as don’t know what your table or model look like. This should work, so something is missing and it will take stepping through each individual part of the formula
To me it is likely not create the MonthTable correctly, as doesn’t seem to be filtering the table right. Are you sure the text in the Holidays and Workdays column is exactly “Work day” because even the BLANK logic isn’t working which is very suspicious.
I’m very confident this is the problem because the BLANK logic isn’t even working. It has to be this. Are there any hidden spaces etc in that name. Anything different.
Maybe break out the IF( and BLANK() logic individually and test it.
I checked again. No Blanks.
Show me your Working Day Sales formula
Formula:
Working Day Sales = CALCULATE([Total Sales], 'Date'[Holidays & WorkDays] = "Work Day")
I also have holidays in that column. Do you think that might be causing the problem?
No shouldn’t matter, send me your model.
This formula, should work. If you can’t send me the model, then it will just take working through each individual part of the formula and understanding why it’s not working.
I would start of the IF( & BLANK() logic. Break that out and understand why this isn’t working (because it should be)
Okay, I will take a look. It should work if it is working for you.
Good morning Sam,
It was actually mistake on my part. Sorry about that! The calculation works great now.
Is there any way I can display this without Date Column? Right now when I remove Date it just sums the sales amount and displays blank in Workday number column.
Ideally, I would like to see like this.
Regards,
Kiran
I figured it out! How to display it without dates.
Thanks! Have a wonderful day
Kiran
The formula will not work now as the columns have no date reference.
Are you sure this is what you need. Because what happens if you want to show multiple months? This would start aggregating each working day across many months if you do this.
I created a column in my date table from the measure (Workday number) and used that as a date display.
Work Day TY = [Workday number]
Ok sure you can do that. If that’s work for what you require that’s great.
Hi Sam,
The formula works great for Current Month & Year but doesn’t work well for Last Year. For some reason, for last year it counts Sunday and Monday as off days and Tuesday through Saturday Work Day.
Work Days for TY = Workdays number TY=
Var
CurrentMonth = SELECTEDVALUE('Date'[Month & Year])
VAR
MonthTable = FILTER(ALL('Date'), 'Date'[Holidays & WorkDays] = "Work Day" && 'Date'[Month & Year] = CurrentMonth)
RETURN
IF (SELECTEDVALUE('Date'[Holidays & WorkDays]) <> "Work Day",
BLANK(),
RANKX(MonthTable, CALCULATE(AVERAGE('Date'[DayOfMonth])), , ASC))
Workdays number ly = CALCULATE([Workdays number TY], SAMEPERIODLASTYEAR('Date'[Date]))
Thanks in advance,
Kiran
Sorry this was 4 months ago so hard to remember what was done exactly.
Just check your workday and weekend column inside your date table, do it actually align with the correct dates?
