topic Based on Quarter Selection Previous 3 Quarter value needed in DAX Commands and Tips

Based on Quarter Selection Previous 3 Quarter value needed

Anonymous — Thu, 22 Oct 2020 17:18:13 GMT

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

Re: Based on Quarter Selection Previous 3 Quarter value needed

camargos88 — Thu, 22 Oct 2020 19:18:35 GMT

Hi Anonymous ,

Check the attached file.

The key point is to have a disconnected date table.

Re: Based on Quarter Selection Previous 3 Quarter value needed

Anonymous — Fri, 23 Oct 2020 05:12:25 GMT

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?

Re: Based on Quarter Selection Previous 3 Quarter value needed

camargos88 — Fri, 23 Oct 2020 18:59:25 GMT

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)))

Re: Based on Quarter Selection Previous 3 Quarter value needed

Anonymous — Sat, 24 Oct 2020 15:23:02 GMT

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

Re: Based on Quarter Selection Previous 3 Quarter value needed

camargos88 — Mon, 26 Oct 2020 13:35:38 GMT

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.