MTD, QTD, YTD Calculation

[AishwariyaV]

Hi,

I have calculated sales YTD, sales MTD and sales QTD. I have included separate slicers as YTD, MTD and QTD. 

If i select Year = 2010, Quarter = 3, my sales YTD has to show sales upto quarter 3 (i.e Q1,Q2 and Q3 sales) and my sales QTD has to show sales only for quarter 3.  Instead, in my report, when year  = 2010 and quarter = 3 is selected, Sales YTD and Sales QTD is showing the same value.  

[v-cherch-msft · Accepted solution]

Hi @AishwariyaV 

You may create slicer tables to achieve that.Make sure there's no relationships.Then you may create measures like below.Attached sample file for your reference.

Sales_QTD =
TOTALQTD (
    SUM ( 'Table'[Sales] ),
    'Table'[Date],
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year] = SELECTEDVALUE ( Slicer_Year[Year] )
            && 'Table'[Qty] = SELECTEDVALUE ( Slicer_Qty[Qty] )
    )
)

Sales_QTD_Total = SUMX('Table',[Sales_QTD])

Sales_YTD = IF(SELECTEDVALUE(Slicer[TImeFrame])="YTD",TOTALYTD(SUM('Table'[Sales]),'Table'[Date],FILTER(ALL('Table'),'Table'[Year]=SELECTEDVALUE(Slicer_Year[Year])&&'Table'[Qty]<=SELECTEDVALUE(Slicer_Qty[Qty]))))

Sales_YTD_Total = SUMX('Table',[Sales_YTD])

Regards,