Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)))))))
Solved! Go to 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
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:
I added column with dax:
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
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?
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
@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
User | Count |
---|---|
128 | |
112 | |
99 | |
65 | |
62 |
User | Count |
---|---|
138 | |
116 | |
102 | |
70 | |
57 |