Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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 🙂

     

5 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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: 

    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.
    • TheoC's avatar
      TheoC
      Community Champion
      4 years ago

      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 🙂

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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