cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
katerinepr
Helper III
Helper III

Date difference using weeks

Hi, I need help on how to get date difference by countries. And I have to calculate first that these countries are working 5 weeks in a month. See below sample dax I am trying to use:

Response Days =
CALCULATE(
FILTER('Data','Data'[Country] = "ID"),((DATEDIFF(WEEKNUM,'Data'[Date of First Query],5),DATEDIFF(MIN(WEEKDAY('Data'[Date of First Query],6))-(MIN(WEEKDAY('Data'[Date of First Query],6)))))))

1 ACCEPTED SOLUTION

Hi @katerinepr 

 

If you are using two date columns to determine the number of days, then how does the number of weeks come into play?  For example, if 15 November 2021 from 13 October 2021, it will give you a specific number of days.  

 

You can create a calculated column like this:

 

Col 1 = IF ( ISBLANK ( 'First Query Response'[Response Date for First Query] ) , TODAY() - 'First Query Response'[Date of First Query] , 'First Query Response'[Response Date for First Query] - 'First Query Response'[Date of First Query] )

 

Just convert the column format type to Whole Number and you will get the number of days.  

 

Once I know more about what you're expecting from the "weeks" angle, I can assist further 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

5 REPLIES 5
katerinepr
Helper III
Helper III

Hi TheoC,

 

I have these columns, Country, Date of First Query, and Response Date for First Query. I need to get the date difference of those 2 dates, and use today's date if the response date for first query is blank. However, I need to filter that ID and ANZ countries has 5 weeks in a month. 

This is the formula I am using in Tableau, and I can't replicate in PowerBI because of the week:
IF ISNULL([Date of First Query]) THEN NULL
ELSEIF NOT ISNULL([Date of First Query]) and ([Country]="ID") and NOT ISNULL([Response Date for First Query]) THEN ((DATEDIFF('week', [Date of First Query], [Response Date for First Query])* 5) + (MIN(DATEPART('weekday', [Response Date for First Query]),6)) - (MIN(DATEPART('weekday', [Date of First Query]),6)))
ELSEIF NOT ISNULL([Date of First Query]) and ([Country]="ID") and ISNULL([Response Date for First Query]) THEN ((DATEDIFF('week', [Date of First Query], [Today])* 5)+(MIN(DATEPART('weekday', [Today]),6))-(MIN(DATEPART('weekday', [Date of First Query]),6)))
ELSEIF NOT ISNULL([Date of First Query]) and ([Country]="ANZ") and NOT ISNULL([Response Date for First Query]) THEN ((DATEDIFF('week', [Date of First Query], [Response Date for First Query])* 5) + (MIN(DATEPART('weekday', [Response Date for First Query]),6)) - (MIN(DATEPART('weekday', [Date of First Query]),6)))
ELSEIF NOT ISNULL([Date of First Query]) and ([Country]="ANZ") and ISNULL([Response Date for First Query]) THEN ((DATEDIFF('week', [Date of First Query], [Today])* 5)+(MIN(DATEPART('weekday', [Today]),6))-(MIN(DATEPART('weekday', [Date of First Query]),6)))
ELSEIF NOT ISNULL([Date of First Query]) and NOT ISNULL([Response Date for First Query]) THEN ([Response Date for First Query]-[Date of First Query])
ELSEIF NOT ISNULL([Date of First Query]) and ISNULL([Response Date for First Query]) THEN ([Today]-[Date of First Query])
END

Here's a sample of my data:

katerinepr_0-1644283037820.png


I added column with dax: 

1Q Response Days = IF(ISBLANK('First Query Response'[Response Date for First Query].[Date]),
DATEDIFF('First Query Response'[Date of First Query].[Date], TODAY(),DAY),DATEDIFF('First Query Response'[Date of First Query].[Date], 'First Query Response'[Response Date for First Query].[Date],DAY))

but I don't know how to add filter for the country.

Hi @katerinepr 

 

If you are using two date columns to determine the number of days, then how does the number of weeks come into play?  For example, if 15 November 2021 from 13 October 2021, it will give you a specific number of days.  

 

You can create a calculated column like this:

 

Col 1 = IF ( ISBLANK ( 'First Query Response'[Response Date for First Query] ) , TODAY() - 'First Query Response'[Date of First Query] , 'First Query Response'[Response Date for First Query] - 'First Query Response'[Date of First Query] )

 

Just convert the column format type to Whole Number and you will get the number of days.  

 

Once I know more about what you're expecting from the "weeks" angle, I can assist further 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Thank you! This works for now. 🙂 I will get back to you once I am able to determine those weeks. Btw, is this correct? the second if?

AccPreparation = if( isblank ('Accuracy Preparation'[Estimate Submission Date]), blank (),
if ('Accuracy Preparation'[Estimate Submission Date] < 'Accuracy Preparation'[Actual Submission Date], "Outside Estimate",
if ('Accuracy Preparation'[Estimate Submission Date] && ISBLANK( 'Accuracy Preparation'[Actual Submission Date]) && 'Accuracy Preparation'[Estimate Submission Date] < TODAY(), "Outside Estimate",
if ('Accuracy Preparation'[Estimate Submission Date] >= 'Accuracy Preparation'[Actual Submission Date], "Within Estimate"))))

Hi @katerinepr ,

 

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. 

 

Do you want to filter for countries with the minimum [Date of First Query] and the maximum [Date of First Query] of 5 weeks apart and named ID/ANZ?


Best Regards,
Winniz

TheoC
Super User
Super User

@katerinepr can you kindly provide an example of the data you're dealing with and what the output of an example calculation is that you would want in your output?

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors