how to check if a date is in between two dates

[frknklcsln]

Hi,

I want to calculate the datediff between Requested Date and Received Date. Let say Requested day is 1/08/2022(d/m/y) and Received Date is 8/08/2022. Datediff is 7. I want to exclude certain days from this diff. Let's say 3/08/2022 is a holiday and 6/08/2022 -7/08/2022 are weekends. So it should exclude these ones and say datediff 4.

I have 2 tables:

Holiday Date:

Date		Company Code
03/08/2022		0035,0023,8821
14/10/2022		MARFL

Company code can contains more than 1 value like the first row. I am using the CONTAINSSTRING function but am open to new ideas.

Shipment table:

ID		Company Code	Requested Date	Received Date
10000214		0035	29/07/2022	5/08/2022
10004145		MAFR	02/08/2022	4/08/2022

DAX should lookup for the company code and take its holiday date.

Just to give an idea; LOOKUPVALUE('Holiday Dates'[Date], 'Holiday Dates'[Company Code], 'Shipment'[Company Code].

And check this holiday date if it is in between the requested date and received date. If it is in this range it should be excluded from counting. Note that there may be multiple holiday dates in between the requested and received date. It should exclude the number of how many days are holidays in between them.

For example,

For 10000214 shipment, Company of 0035's holiday dates is 2/08/2022, 03/08/2022, 04/08/2022. The output should be 2. Because 30-31/08/2022 are weekends. And 3 days are holidays. It should exclude both holidays and weekends when calculating the datediff.

Hope it's clear. Your help is appreciated.

[Anonymous]

Hi @frknklcsln ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

HI @frknklcsln,

You can try to use the following formulas if it suitable for your requirement:

formula=
NETWORKDAYS ( Table[Requested Date], Table[Received Date], 1, VALUES ( Holiday[Date] ) )

Regards,

Moonlight

[frknklcsln]

Hi @Anonymous ,

Thank you for the answer. But unfortunately, holiday dates change by each company. So, I have to give this info to the function with company code wise. Otherwise, If a company is in holiday 09/08/2022, all other companies will be in holiday too. But they are not. Every company has their own holiday dates. Hope its clear.

[Anonymous]

Hi @frknklcsln,

You can add variable stored current company code and use it in holiday table to get date list first, then use it in the dax functions to get differences:

formula =
VAR currCompany =
    SELECTEDVALUE ( Table[CompanyCode] )
VAR holidayList =
    CALCULATETABLE (
        VALUES ( Holiday[Date] ),
        FILTER (
            ALLSELECTED ( Holiday ),
            SEARCH ( currCompany, [CompanyCode], 1, BLANK () ) > 0
        )
    )
RETURN
    NETWORKDAYS ( Table[Requested Date], Table[Received Date], 1, holidayList )

Regards,

Moonlight

[frknklcsln]

Hi @Anonymous , thank you for your effort. The visual will be country vs networkdays. We will see each country's networkdays. Therefore, I cannot use below code:

SELECTEDVALUE ( Table[CompanyCode] )

[frknklcsln]

Hi @Anonymous ,

Thank you for the answer. But unfortunately, holiday dates change by each company. So, I have to give this info to the function with company code wise. Otherwise, If a company is in holiday 09/08/2022, all other companies will be in holiday too. But they are not. Every company has their own holiday dates. Hope its clear.

[parry2k]

@frknklcsln that's part of your data model, you need to store holidays by each company and then based on the company, filter holidays table and pass that to this function. 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[frknklcsln]

@parry2k , thank you unfortunately I couldn't understand what you mean. Can you write the DAX so I can convert it to my version?

[parry2k]

@frknklcsln yes you can also pass the table for the holidays, 4th parameter can be used to provide the holidays. There is an example of holidays on the same page.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[frknklcsln]

@parry2k , Ok. Another question, how will it understand that each companies holiday? How can I specify that? Because the function requires a table with one single date column for the holiday part. 

[parry2k]

@frknklcsln the new DAX function NETWORKDAYS should get you what you are looking for NETWORKDAYS – DAX Guide

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[frknklcsln]

@parry2k , thank you. What about the holiday part? Do you have any idea to do this?