IF statement issue

[Anonymous]

I have a data that provides values of sales for 2 years but on quarterly basis, Example: 

Year	Quarter	Sales
2020	Q1	50
2020	Q2	30
2020	Q3	40
2020	Q4	50
2021	Q1	60
2021	Q2	70
2021	Q3	50
2021	Q4	40

I need to calculate the sum of sales for the quarters previous to the chosen quarter (giving a slicer for the same) 

Till now I have used the following DAX, but am unsuccessful:

var sel = SELECTEDVALUE(Table_name[Quarter])

var SumQ1 = SUMX(FILTER(ALLEXCEPT('Table_name','Table_name'[Year]),'Table_name'[Quarter]="Q1"),'Table_name'[Sales])

var SumQ2 = SUMX(FILTER(ALLEXCEPT('Table_name','Table_name'[Year]),'Table_name'[Quarter]="Q2"),'Table_name'[Sales])

var SumQ3 = SUMX(FILTER(ALLEXCEPT('Table_name','Table_name'[Year]),'Table_name'[Quarter]="Q3"),'Table_name'[Sales])

var val =

IF(sel="Q1",0,

IF(sel="Q2",SumQ1,

IF(sel="Q3",(SumQ2+SumQ1),

IF(sel="Q4",(SumQ2+SumQ1+SumQ3),"NA"

)

)

)

)

return val

ANY IDEA TO SOLVE THIS?

[amitchandak · Accepted solution]

@Anonymous , With Time intelligence, create QTD and YTD and table a diff

example

QTD= CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

YTD= CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Till Last Qtr = [YTD] -[QTD]