Forum Discussion

aloosh89's avatar
aloosh89
Helper I
2 years ago
Solved

Checking if difference between two dates is in a certain quarter

Hello,

 

I am trying to use a FILTER command in a dax expression for my dataset in which each row has attributes 'Start Date' and 'End Date'. I want to be able to check, if any portion of time period between the Start and the End dates is within a specific quarter. Let's say Quarter 1 of the year 2023. I have given a sample table below of two instances of the data (the date columns are already coded as date data type). For this example table I gave, the first row would be 'True' meaning it is in Q1 2023 while the second one would be 'False' that it is in Q12023. I am looking for an expression that would evaluate to true or false so I can apply my filter in what I am trying to do. The last column i have in my example table is not part of the dataseet but is what I want the boolean check to achieve

 

Help would be apprciated and thanks in advance.

 

Ali 

 

IDStart DateEnd DateIn Q1 2023?
11-Nov-221-Feb-23True
23-Dec-2310-Dec-23False
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi aloosh89 
    If you need this flag as a "static" flag you can use the following dax measure :

    Flag =
    var
    startdate_check= if(QUARTER(max('fact'[start date]))=1 && YEAR(max('fact'[Start Date]))=2023,1,0)
    var
    enddatdate_check= if(QUARTER(max('fact'[end date]))=1 && YEAR(max('fact'[End Date]))=2023,1,0)
    return
    if (startdate_check=1 || enddatdate_check=1,TRUE(),FALSE())

    If you need this more dynamically then:
    you can create a "quarters dictionary" disconnected  table like :

    and use measure :

    Flag_Dynamic =
    var
    startdate_check= if(QUARTER(max('fact'[start date]))=QUARTER(max('Quarters dictionary'[start date])) && YEAR(max('fact'[Start Date]))=year(max('Quarters dictionary'[start date])),1,0)
    var
    enddatdate_check= if(QUARTER(max('fact'[end date]))=QUARTER(max('Quarters dictionary'[end date])) && YEAR(max('fact'[End Date]))=year(max('Quarters dictionary'[start date])),1,0)
    return
    if (startdate_check=1 || enddatdate_check=1,TRUE(),FALSE())

     

    Link to a sample file 

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

7 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi aloosh89 
    If you need this flag as a "static" flag you can use the following dax measure :

    Flag =
    var
    startdate_check= if(QUARTER(max('fact'[start date]))=1 && YEAR(max('fact'[Start Date]))=2023,1,0)
    var
    enddatdate_check= if(QUARTER(max('fact'[end date]))=1 && YEAR(max('fact'[End Date]))=2023,1,0)
    return
    if (startdate_check=1 || enddatdate_check=1,TRUE(),FALSE())

    If you need this more dynamically then:
    you can create a "quarters dictionary" disconnected  table like :

    and use measure :

    Flag_Dynamic =
    var
    startdate_check= if(QUARTER(max('fact'[start date]))=QUARTER(max('Quarters dictionary'[start date])) && YEAR(max('fact'[Start Date]))=year(max('Quarters dictionary'[start date])),1,0)
    var
    enddatdate_check= if(QUARTER(max('fact'[end date]))=QUARTER(max('Quarters dictionary'[end date])) && YEAR(max('fact'[End Date]))=year(max('Quarters dictionary'[start date])),1,0)
    return
    if (startdate_check=1 || enddatdate_check=1,TRUE(),FALSE())

     

    Link to a sample file 

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • aloosh89's avatar
      aloosh89
      Helper I
      2 years ago

      Ritaf1983 thanks for providing this method. Could you please take a look at my reply below in the thread where I added a new row explaining what I want to capture for a case that wasn't covered in my original post? Thanks for your help. 

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    pls try this

    In Q1 2023? = 
VAR __Q1 = CALENDAR(DATE(2023,1,1),DATE(2023,3,31))
RETURN
[Start Date] in __Q1 || [End Date] in __Q1

    • aloosh89's avatar
      aloosh89
      Helper I
      2 years ago

      Ahmedx  thanks for sharing. I see you are using start date and end date as measures. For me they are columns in a table, hence when I use the code you provided I can't capture the dates and the code has an error. Additionally, please see my reply below where I have added one more row to the data to represent what I am trying to do better. Thanks for your help.

  • aloosh89's avatar
    aloosh89
    Helper I
    2 years ago

    Hello, 

     

    Sorry but I should have given more representative data. Consider the new row I added where neither the start date nor the end date fall in Q12023. Yet in the period between the start and the end, Q12023 occurs. I want to be able to detect 'TRUE' for that as well. I basically need to get true for any start/end date range in which Q12023 is spanned, whether for part of the quarter or the entire.

     

    IDStart DateEnd DateIn Q1 2023?
    11-Nov-221-Feb-23Yes
    23-Dec-2310-Dec-23No
    31-Nov-225-Jul-23yes
  • aloosh89's avatar
    aloosh89
    Helper I
    2 years ago

    I managed to find the solution. Just had to simplify. I put the following boolean checks in my filter and got the result I need. Inside my calculate expression, i applied the below filter (for Q32023). Same logic for any quarter. Brackets may be off as I truncated some other parts of the filter expression, but the boolean logic is correct. Thanks for all your help!

     

    filter('table',  
       
        (('Table'[START_DATE].[QuarterNo]=3 && 'Table'[START_DATE].[Year]=2023) || ('Table'[END_DATE].[QuarterNo]=3 && 'Table'[END_DATE].[Year]=2023))
    || ('Table'[START_DATE]<DATE(2023, 07, 01) && 'Table'[END_DATE]>DATE(2023,09,30))))