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 achieve this?)

4. Others

 

This is my code...

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

5 Replies

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity 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
        Icon for Community Support rankCommunity 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.