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

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