Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rolling 4 Quarter Flag

Hi,

 

I'm trying to create a calculated column in my date table that I can use as a filter that is a rolling 4 quarters. So from the start of the current quarter and then the following 3. Here is my current formula but it isn't working... I feel like I'm close. Does anyone have any suggestions? 

 
Test =
VAR __Today = TODAY()
VAR __QuarterStart =
CALCULATE(
STARTOFQUARTER('Date'[Date]),
'Date'[Year] = Year(__Today),
'Date'[QuarterNo] = QUARTER(__Today))
VAR __QuarterEnd =
CALCULATE(
STARTOFQUARTER('Date'[Date]),
'Date'[Year] = Year(__Today)+1,
'Date'[QuarterNo] = QUARTER(__Today))
RETURN
IF('Date'[Date] >= __QuarterStart && 'Date'[Date]<__QuarterEnd, 1,0)

 

Thanks! 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Please try to add ALL() functions in both calculated exporessions:

    Test = 
VAR _Today =
    TODAY ()
VAR _QuarterStart =
    CALCULATE (
        STARTOFQUARTER ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year] = YEAR ( _Today )
                && 'Date'[QuarterNo] = QUARTER ( _Today )
        )
    )
VAR _QuarterEnd =
    CALCULATE (
        STARTOFQUARTER ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year]
                = YEAR ( _Today ) + 1
                && 'Date'[QuarterNo] = QUARTER ( _Today )
        )
    )
RETURN
    IF ( 'Date'[Date] >= _QuarterStart && 'Date'[Date] < _QuarterEnd, 1, 0 )

    Attached a sample file in the below, hopes it could help.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

    Please try to add ALL() functions in both calculated exporessions:

    Test = 
VAR _Today =
    TODAY ()
VAR _QuarterStart =
    CALCULATE (
        STARTOFQUARTER ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year] = YEAR ( _Today )
                && 'Date'[QuarterNo] = QUARTER ( _Today )
        )
    )
VAR _QuarterEnd =
    CALCULATE (
        STARTOFQUARTER ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year]
                = YEAR ( _Today ) + 1
                && 'Date'[QuarterNo] = QUARTER ( _Today )
        )
    )
RETURN
    IF ( 'Date'[Date] >= _QuarterStart && 'Date'[Date] < _QuarterEnd, 1, 0 )

    Attached a sample file in the below, hopes it could help.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.