YTD and MAT values needs to be calculated base on the slicer selection

[Anonymous]

Hi,

For Ex: if I select Q2 2020 from the quarter slicer,

Then YTD = Q1 2020 + Q2 2020

          MAT = Q3 2019 + Q4 2019 +Q1 2020 + Q2 2020

          QTR = Current Quarter selected

if I select Q3 2020 from the quarter slicer,

Then YTD = Q1 2020 + Q2 2020 + Q3 2020

          MAT = Q4 2019 +Q1 2020 + Q2 2020 + Q3 2020

          QTR = Current Quarter selected

My slicer fileds are : YTD, MAT, QTR

Please some one help me with the DAX

Many Thanks,

Pallavi

[amitchandak]

@Anonymous , if you have date 

If you have Qtr year, Have table with rank on YYYYQ

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

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

Rolling 4 Qtr

Rolling 4 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-4,Quarter))

New column

Qtr Rank = RANKX(all('Date'),'Date'[Year Qtr],,ASC,Dense)

Measures 

This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

Last 4 Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]>=max('Date'[Qtr Rank])-4 && 'Date'[Qtr Rank]<=max('Date'[Qtr Rank]) ))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Qtr] <= Max('Date'[Qtr]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Qtr] <= Max('Date'[Qtr])))

[Anonymous]

Hey Amit, 

I tried with above dax formula, Its confusing to build this.

Please help me with demo file if possible, it would be really helpfull