Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

IF statement issue

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

YearQuarterSales
2020Q150
2020Q230
2020Q340
2020Q450
2021Q160
2021Q270
2021Q350
2021Q440

 

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's avatar
    amitchandak
    4 years ago

    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]

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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]