Based on Quarter Selection Previous 3 Quarter value needed

[Anonymous]

Hi all,

I have added the quarter as a filter in this filter if i select 2020 Q3 i need previous 3 quarter value visible in cluster column chart

saple data 

Country	Job	Value	Date	Quarter
Netherlands	Business	1	6/1/2019	Q2
Netherlands	Finance	1	6/1/2019	Q2
United Kingdom	Business	0.6	6/1/2019	Q2
United Kingdom	Business	0.6	6/1/2019	Q2
Netherlands	Business	0.9	9/1/2019	Q3
Netherlands	Business	1	9/1/2019	Q3
Netherlands	Finance	1	9/1/2019	Q3
United Kingdom	Business	1	1/1/2020	Q1
United Kingdom	Business	0.6	1/1/2020	Q1
United Kingdom	Business	1	1/1/2020	Q1
United Kingdom	Finance	0.8	4/1/2020	Q2
United Kingdom	Business	1	4/1/2020	Q2
United Kingdom	Business	1	4/1/2020	Q2

how can I achieve this logic?

Thanks & Regards,

Rajesh R

[camargos88]

Hi @Anonymous ,

Check the attached file.

The key point is to have a disconnected date table.

[Anonymous]

@camargos88

 if i select 2020 Q3  i need  selected quarter and previous 3 quarter value ie   2020 Q3,  2020 Q2,   2020 Q1 ,   2019 Q4

is this possible?

[camargos88]

@Anonymous ,

Try this measure:

_Value = 
VAR _selectedDate = MAX(D_Date[Date])
RETURN CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Date] in DATESINPERIOD(D_Date[Date], _selectedDate, -3, QUARTER)))

[Anonymous]

@camargos88 

Thanks for your response, i have tried your measure but it's not working

if i select 2020 Q3 previous 3 quarter value display 2020Q2, 2020Q1, 2019Q4

i have attached my pbix file click here,

Thanks 

Rajesh

[camargos88 · Accepted solution]

@Anonymous ,

Change the measure:

_Value = 
VAR _selectedDate = MAX(D_Date[Date])
RETURN CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Date] in DATESINPERIOD(D_Date[Date], _selectedDate, -4, QUARTER)))

Also, I've included a new line in the source for 2019 Q14 as 20191201.