Forum Discussion

AishwariyaV's avatar
AishwariyaV
Icon for Helper IV rankHelper IV
7 years ago
Solved

MTD, QTD, YTD Calculation

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.  

Need Help
Show and Tell
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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,