topic Re: YTD and MAT values needs to be calculated base on the slicer selection in DAX Commands and Tips

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

Anonymous — Wed, 14 Jul 2021 06:00:05 GMT

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

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

amitchandak — Wed, 14 Jul 2021 08:38:02 GMT

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])))

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

Anonymous — Wed, 14 Jul 2021 11:23:51 GMT

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