Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

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
Anonymous
Not applicable

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

 

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

Anonymous
Not applicable

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 @Anonymous ,

 

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

@Anonymous 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.