Rolling 5 quarter restriction based on Year-Quarter Selection Filter

[Anonymous]

Hi all 

I want to show rolling 5 quarter in my chart 

Ex: I have selected 2019 Q3 in the Quarter selection filter so i want to show following quarter in Cumulative sales Trend 

1. Current Quarter =  2019 Q3
2. Previous Quarter = 2019 Q2
3. This Quarter in Previous Year = 2018 Q3
4. Other Quarte = 2019 Q1, 2018 Q4

Data:

Account Id	Opportunity Id	Amount	Close Date	User Name
A1	Opp1	$150,000	2/1/2018	Sean Barton
A1	Opp2	$40,000	5/28/2018	Sean Barton
A1	Opp3	$10,000	8/21/2018	Rachel Stark
A1	Opp4	$20,000	10/18/2018	Sean Barton
A1	Opp5	$15,000	11/7/2018	Sean Barton
A1	Opp6	$75,000	3/26/2019	Sean Barton
A1	Opp7	$85,000	5/30/2019	Sean Barton
A1	Opp8	$150,000	6/15/2019	Rachel Stark
A1	Opp9	$15,000	9/5/2019	Rachel Stark
A1	Opp10	$50,000	10/16/2019	Sean Barton
A2	Opp11	$120,000	3/1/2018	Sean Barton
A2	Opp12	$5,000	6/3/2018	Rachel Stark
A2	Opp13	$10,000	9/20/2018	Sean Barton
A2	Opp14	$5,000	11/24/2018	Rachel Stark
A2	Opp15	$50,000	12/31/2018	Sean Barton
A2	Opp16	$120,000	2/14/2019	Sean Barton
A2	Opp17	$75,000	5/18/2019	Sean Barton
A2	Opp18	$75,000	5/29/2019	Rachel Stark
A2	Opp19	$85,000	7/31/2019	Sean Barton
A2	Opp20	$85,000	10/31/2019	Sean Barton
A2	Opp21	$5,000	11/28/2019	Sean Barton
A3	Opp22	$50,000	2/22/2018	Rachel Stark
A3	Opp23	$100,000	4/28/2018	Sean Barton
A3	Opp24	$40,000	8/1/2018	Rachel Stark
A3	Opp25	$35,000	8/31/2018	Sean Barton
A3	Opp26	$5,000	11/14/2018	Sean Barton
A3	Opp27	$10,000	10/29/2018	Rachel Stark
A3	Opp28	$85,000	2/5/2019	Rachel Stark
A3	Opp29	$40,000	6/3/2019	Sean Barton
A3	Opp30	$75,000	7/26/2019	Rachel Stark
A3	Opp31	$5,000	12/21/2019	Sean Barton

Following Things I tried:

Year-Q = Opportunity[Close Date].[Year] & "-Q" & Opportunity[Close Date].[QuarterNo]

Quarter Start Date = 
VAR TodaysDate = Opportunity[Close Date]
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"Quarter",ROUNDUP(MONTH([Date])/3,0))
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2))
VAR TodaysMonth = ROUNDUP(MONTH(TodaysDate)/3,0)
VAR Calendar4 = FILTER(Calendar3,[Quarter]=TodaysMonth&&[WeekDay1]<6)
VAR FirstWorkingDay = MINX(Calendar4,[Date])
RETURN FORMAT(FirstWorkingDay,"mm/dd/yyyy")

Week Number = DATEDIFF(Opportunity[Quarter Start Date],Opportunity[Close Date], WEEK ) +1

Sales running total by Week = 
CALCULATE(
	SUM(Opportunity[Amount]),
	FILTER(
		ALLSELECTED(Opportunity[Week Number]),
		ISONORAFTER(Opportunity[Week Number], MAX(Opportunity[Week Number]), DESC)
	)
)

Finally, I have created a chart but I can't restrict rolling 5 quarter Trend It shows All Quarter Value.

how can I achieve this logic?

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

First of all, we create a calculated table as slicer using following formula:

Table =
ADDCOLUMNS (
    CROSSJOIN ( DISTINCT ( 'Opportunity'[Close Date].[Year] ), { 1, 2, 3, 4 } ),
    "Year-Q", [Close Date].[Year] & "-Q" & [Value]
)

Then create Five measures to meet your requirement:

Current Quarter = 
IF (
    HASONEVALUE ( 'Table'[Year-Q] ),
    CALCULATE (
        SUM ( Opportunity[Amount] ),
        FILTER (
            ALLSELECTED ( Opportunity[Week Number] ),
            ISONORAFTER ( Opportunity[Week Number], MAX ( Opportunity[Week Number] ), DESC )
        ),
        FILTER ( ALLSELECTED ( Opportunity ), [Year-Q] = VALUES ( 'Table'[Year-Q] ) )
    )
)

Previous Quarter = 
IF (
    HASONEVALUE ( 'Table'[Year-Q] ),
    CALCULATE (
        SUM ( Opportunity[Amount] ),
        FILTER (
            ALLSELECTED ( Opportunity[Week Number] ),
            ISONORAFTER ( Opportunity[Week Number], MAX ( Opportunity[Week Number] ), DESC )
        ),
        FILTER (
            ALLSELECTED ( Opportunity ),
            [Year-Q]
                = LOOKUPVALUE (
                    'Table'[Year-Q],
                    'Table'[Year], IF (
                        MAX ( 'Table'[Value] ) = 1,
                        CALCULATE ( MAX ( 'Table'[Year] ) ) - 1,
                        CALCULATE ( MAX ( 'Table'[Year] ) )
                    ),
                    'Table'[Value], IF ( MAX ( 'Table'[Value] ) = 1, 4, CALCULATE ( MAX ( 'Table'[Value] ) ) - 1 )
                )
        )
    )
)

This Quarter in Previous Year = 
IF (
    HASONEVALUE ( 'Table'[Year-Q] ),
    CALCULATE (
        SUM ( Opportunity[Amount] ),
        FILTER (
            ALLSELECTED ( Opportunity[Week Number] ),
            ISONORAFTER ( Opportunity[Week Number], MAX ( Opportunity[Week Number] ), DESC )
        ),
        FILTER (
            ALLSELECTED ( Opportunity ),
            [Year-Q]
                = LOOKUPVALUE (
                    'Table'[Year-Q],
                    'Table'[Year], CALCULATE ( MAX ( 'Table'[Year] ) ) - 1,
                    'Table'[Value], CALCULATE ( MAX ( 'Table'[Value] ) )
                )
        )
    )
)

Previous Two Quarter = 
VAR q =
    SWITCH ( CALCULATE ( MAX ( 'Table'[Value] ) ), 1, 3, 2, 4, 3, 1, 4, 2 )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Year-Q] ),
        CALCULATE (
            SUM ( Opportunity[Amount] ),
            FILTER (
                ALLSELECTED ( Opportunity[Week Number] ),
                ISONORAFTER ( Opportunity[Week Number], MAX ( Opportunity[Week Number] ), DESC )
            ),
            FILTER (
                ALLSELECTED ( Opportunity ),
                [Year-Q]
                    = LOOKUPVALUE (
                        'Table'[Year-Q],
                        'Table'[Year], IF (
                            MAX ( 'Table'[Value] ) <= 2,
                            CALCULATE ( MAX ( 'Table'[Year] ) ) - 1,
                            CALCULATE ( MAX ( 'Table'[Year] ) )
                        ),
                        'Table'[Value], q
                    )
            )
        )
    )

Previous Three Quarter = 
VAR q =
    SWITCH ( CALCULATE ( MAX ( 'Table'[Value] ) ), 1, 2, 2, 3, 3, 4, 4, 1 )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Year-Q] ),
        CALCULATE (
            SUM ( Opportunity[Amount] ),
            FILTER (
                ALLSELECTED ( Opportunity[Week Number] ),
                ISONORAFTER ( Opportunity[Week Number], MAX ( Opportunity[Week Number] ), DESC )
            ),
            FILTER (
                ALLSELECTED ( Opportunity ),
                [Year-Q]
                    = LOOKUPVALUE (
                        'Table'[Year-Q],
                        'Table'[Year], IF (
                            MAX ( 'Table'[Value] ) <= 3,
                            CALCULATE ( MAX ( 'Table'[Year] ) ) - 1,
                            CALCULATE ( MAX ( 'Table'[Year] ) )
                        ),
                        'Table'[Value], q
                    )
            )
        )
    )

BTW, pbix as attached.

Best regards,

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

[Anonymous]

Thank You @v-lid-msft 

Now I got the Expected Result. I spent almost two days on this result. Thank Very much for sharing your knowledge 

[amitchandak]

One of the ways is to use visual or page level filters. There you can use relative date filtering. But It only has option week, month and year.

Or use a formula like this

five_qtr_data = 
Var _start_date= minx(ALLSELECTED('Date'),STARTOFMONTH(dateadd('Date'[Date Filer],-5,QUARTER)))
Var  _end_date =maxx(ALLSELECTED('Date'),ENDOFMONTH('Date'[Date Filer]))

Var _5_qtr_data= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date)
return
_5_qtr_data

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

[Anonymous]

Thanks @amitchandak  for the quick response

I tried Your solution but not getting expected result 

can you please share your Pbix file which you have tried using my sample data.