Help on showing previous quarter value based on slicer selector without data table

[Timo1980]

Hi All,

below a simplified table as source. In need to make a barchart where the slicers would be one for year and one for quarter.

Now based on the quarter slicer, the barchart should show the previous month(s) and value. So if we are in Q2 then X-axis should show Q1 and Q2 savings. And when i select Q3, it would show then the values of Q1-2-3 etc. Now the source data does not have date tables, the years and quarters are whole numbers. What is the best solution to sort this?

Sales        OrgYear    Quarter   Saving

A	2020	1	2
A	2020	2	3
A	2020	3	6
A	2020	4	8
A	2021	1	1
A	2021	2	5
A	2021	3	7
A	2021	4	8

[amitchandak]

@Timo1980 , You need to create a combined key

Year Qtr = [Year]*100 +[Qtr]

Create a new table with Qtr, Year and Year Qtr and do not join this Qtr with your table

Create a measure like

Meausre =

var _year = maxx(allselected(QTR), QTR[Year])

return

calculate(Sum(Table[saving]), filter(Table, Table[Year] =_year))

[Timo1980]

hi amit, im getting a syntax error "The syntax for 'Table' is incorrect. (DAX(var _year = maxx(allselected(QTR), QTR[Year ])returncalculate(Sum('Table'[Saving]), filter(Table, 'Table'[Year ] =_year))))."

In the syntax error descr. it adds 2 more brackets and . for some reason

I have named the second table QTR.  And on the meaure above, is filtering on the year? because the main filter would be quarter.  seems im not allowed to upload the pbix file. Below screen is showing what i see when i filter on Q3. And i would like to also see Q1 and Q2 in the graph when i select Q3.

[V-lianl-msft · Accepted solution]

First create a disconnected quarter table as a slicer.

qtr_slicer = DISTINCT('Table'[QTR])

Then create a measure like below:

Measure = 
var selected_qtr = MAX(qtr_slicer[QTR])
return CALCULATE(SUM('Table'[Saving]),FILTER('Table','Table'[QTR]<=selected_qtr))