Count of non-holiday fridays

[Suna]

Hello dax wizards!

Long question short: How to count the number of fridays unless that day is a public holiday. If it is, then calculate the previous non-holiday. And I need a measure because I'm using this measure in another measures.

Long question long:

Background: We have three sales Group, lets say A, B and C. Sales happen biweekly for A and B group, even weeks are for group A and odd weeks are for group B. Group C has sales on the last workday of each month. For the later calculations I need to leave out gourp C.

A and B sales happen every friday, unless that day is a public holiday (then it's the previous workday). Every quarter there is one month that has 3 fridays for one of the groups. I have a salesgraph that I would need to get a measure for calculatory sales, ie if one group have 3 salesday in a month, the sales amount needs to be divided by 3 and multiplied by 2 (to get every month to 2 salesperiod). For this measure I first need to calculate how many salesdays there is in a month. Maybe this helps:

I have calendar table that has columns for public holidays and salesdays, both as "yes" or "no", but they can be changed to 0 or 1 if neccessary. There is also a column that says if the week is for Group A or B. And the number of weekday (monday =1).

My first attempt was to just count fridays. It worked very well untill I realized the problem with public holidays. For example April 2020 has 3 fridays and 2 thursdays that are salesdays.

Next I tried the salesday approach:

CALCULATE(COUNTROWS(Calendar), Calendar[Salesday] = "Yes")

But ofcourse this counts also Group C salesday, so 1 week has two salesdays, unless the last workday of the month is friday. This would be the easiest measure (I think), if there could be a section that leaves the last salesday of the month out, unless that day is friday.

Or a measure counting the last non-holiday workday of each week.

Sorry for the long post, I never know how much background info to give that the question is understood 🙂

[v-yingjl]

Hi @Suna ,

Based on your description, to calculate non-holiday of fridays, try like this:

Create a weekday column:

Weekday = WEEKDAY('Table'[Date],2)  //Monday = 1

Create a measure to calculate fridays:

count fri = 
CALCULATE(
    DISTINCTCOUNT('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Weekday] = 5 &&
        'Table'[Salesday] = "Yes"
    )
)

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Suna]

This I already tried. It gives right result only, if the salesday is friday. Sometimes it's thursday, sometimes it can be wednesday or tuesday, depending how many public holidays there are in that week.

So if this measure could add a section "if weekday = 5 and holiday = Yes then count previous salesday instead" it could work.

Or if there could be something like countrows of calendar if salesday = yes but don't count the last one if it is not friday. the end result will be diagram by month so that measure would leave out Group C.

I'm pretty much already lost hope with this case. I just don't think there is a way to count all these factors to get the right result 😞

[Suna]

@v-yingjl 

I forgot to tag you in prev post. I just realized that if I can make a column in the calendar for Groups A-C I could use that to leave out C of salesday count. 

I already have a column for A and B because that was easy to do back in the day 🙂

IF(MOD(Calendar[Week], 2) =0 , "GA", "GB")

If I could add C to a new column or the same one that could help. But here I always face a wall of how to do it because I can't figure how to get last salesday of the month. I was thinking usin EOMONTH but the last salesday may not be the actual last day of the month. So it doesn't work but this was closest I got.

IF (Calender [Date] = EOMONTH (Calended [Date] , 0) &&

Calender[Date] = "Yes",

"Yes" , BLANK ( ) )

End result what I would be looking for:

date	Weeknumber	Salesweek	Group C
15.4.2020	16	Group A
16.4.2020	16	Group A
20.4.2020	16	Group A
21.4.2020	16	Group A
22.4.2020	17	Group B
23.4.2020	17	Group B
27.4.2020	17	Group B
28.4.2020	17	Group B
29.4.2020	18	Group A
30.4.2020	18	Group A	Yes
1.5.2020	18	Group A
2.5.2020	18	Group A
3.5.2020	18	Group A
4.5.2020	18	Group A

[Suna]

Actually no, this woudn't work either.  Because Group C salesday can be Group A or B salesday too so using this as a measure would not give the right answer. So back to square 1 I guess.

[Suna · Accepted solution]

I figured this out 🙂 It's not 100 % effective but it works almost every month well enough.

I don't think anybody else needs this solution but what I did was (shortly)

- Column for C salesday

- Column friday GC: if C salesday is "yes" and if weekday is 5

Using these I made column for salesdays that are not "no" in the column friday GC