cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Suna
Helper I
Helper I

Count of non-holiday fridays

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:

 

Calendar.jpg

 

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 🙂

1 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 

View solution in original post

5 REPLIES 5
v-yingjl
Community Support
Community Support

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"
    )
)

table.pngtable result.png

 

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.

 

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 😞

@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:

 

dateWeeknumberSalesweekGroup C
15.4.202016Group A 
16.4.202016Group A 
20.4.202016Group A 
21.4.202016Group A 
22.4.202017Group B 
23.4.202017Group B 
27.4.202017Group B 
28.4.202017Group B 
29.4.202018Group A 
30.4.202018Group AYes
1.5.202018Group A 
2.5.202018Group A 
3.5.202018Group A 
4.5.202018Group A 

 

 

 
 

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.

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 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors