Forum Discussion

alienlin's avatar
alienlin
Frequent Visitor
6 years ago

Logical IF with overlapping condition

Hi all, I am struggling with this dax measure in my table. The result I want is  1. Last quarter 2. Current quarter 3. 5 quarters (this include current quarter and next 4 quarters --> How do I ach...
Need Help
v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi alienlin ,

Maybe the measure is like this? I have modified the locgic about "Last Quarter" and "5 Quarters"

DateRange =
IF (
    (
        QUARTER ( TODAY () ) - QUARTER ( 'Milestones'[End_Date] ) = 1
            && YEAR ( TODAY () ) = YEAR ( 'Milestones'[End_Date] )
    )
        || (
            QUARTER ( TODAY () ) - QUARTER ( 'Milestones'[End_Date] ) = -3
                && YEAR ( TODAY () ) - YEAR ( 'Milestones'[End_Date] ) = 1
        ),
    "Last Quarter",
    IF (
        QUARTER ( TODAY () ) = QUARTER ( 'Milestones'[End_Date] )
            && YEAR ( TODAY () ) = YEAR ( 'Milestones'[End_Date] ),
        "Current Quarter",
        IF (
            QUARTER ( TODAY () ) = QUARTER ( 'Milestones'[End_Date] )
                && YEAR ( TODAY () ) - YEAR ( 'Milestones'[End_Date] ) = -1,
            "5 Quarters",
            "Others"
        )
    )
)

 

Best Regards,
Yingjie Li

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

alienlin's avatar
alienlin
Frequent Visitor
6 years ago

Thanks v-yingjl, I see the improvement of 'Last quarter' but I still cannot get the '5 quarters' right. Because '5 quarters' should include 'Current quarter' and the next 4 quarters. So when we define 'Last quarter' and '5 quarters', I lose 'Current quarter'. 

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi alienlin ,

    Since '5 quarters' includes 'Current quarter' and the next 4 quarters. It means the same quarter but just next year right? How can the 'Current Quarter' will be lost? It should work.

    Best Regards,
    Yingjie Li

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

    • alienlin's avatar
      alienlin
      Frequent Visitor
      6 years ago

      hi v-yingjl , '5 quarters' can also start from 2020-Q2, it will be 2020-Q2 until 2021-Q2, that is why I cannot get the code work as the way I want it. It ended up 'Current quarter' is missing. Thanks a lot for your reply, though. 🙂

      • sudhakar111's avatar
        sudhakar111
        Helper IV
        6 years ago

        Hello, I have a similar requirement. I want to create a filter with values 7,30 and 60.

         

        Here the Date_Condtion field is a substraction between 2 dates. 

         

        The problem is with the overlapping conditions. If 30 is selected it displays dates from 8th day to 30th.It ignores the first 7 days,due to overlapping condition. Please let me know if you found a solution.

         

        Days_Filter = if(('Exchange'[Date_Condition]>=1 &&'Exchange'[Date_Condition]<=7),7,if(('Exchange'[Date_Condition]>=1 && 'Exchange'[Date_Condition]<=30),30,if(('Exchange'[Date_Condition]>=1 && 'Exchange'[Date_Condition]<=60),60)))