Forum Discussion

aloosh89's avatar
aloosh89
Icon for Helper I rankHelper I
3 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
    3 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

Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
3 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
    Icon for Helper I rankHelper I
    3 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.