cancel
Showing results for
Did you mean:
Highlighted
Helper III

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: MTD, QTD, YTD Calculation

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Microsoft

## Re: MTD, QTD, YTD Calculation

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors