IF statement issue

Anonymous — Wed, 29 Sep 2021 08:39:43 GMT

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

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?

Re: IF statement issue

amitchandak — Wed, 29 Sep 2021 08:49:30 GMT

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]