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 peri...
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

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