# How to calculate subtotal correctly when using sameperiodlastyear

• ### Question

• Hi everyone, I am trying to calculate number of orders in a certain periods and compare it with the same period of last year.

Problem is that I do get correct numbers for each rows, but I get wrong for subtotal. Could anyone help me to figure out how to calculate correctly for subtotals?

Applied DAX are,

ordercount = DISTINCTCOUNT(orders[ordernumbers])

PYordercount = CALCULATE(DISTINCTCOUNT(orders[ordernumbers]), SAMEPERIODLASTYEAR(shipment[shippingdate]))

 Orderdate ordercount PYordercount 2019/7/4 37 39 2019/7/5 53 50 2019/7/6 52 24 2019/7/7 34 34 2019/7/8 19 33 2019/7/9 32 28 2019/7/10 56 37 2019/7/11 40 38 subtotal 323 993

Hisashi

Saturday, July 13, 2019 1:19 AM

• Hi Hisashi,

Try this measure:

```PYordercount =
SUMX (
VALUES ( orders[Orderdate] ),
CALCULATE (
DISTINCTCOUNT ( orders[ordernumbers] ),
SAMEPERIODLASTYEAR ( shipment[shippingdate] )
)
)```
• Marked as answer by Monday, July 15, 2019 5:13 AM
Saturday, July 13, 2019 9:42 PM

### All replies

• Hi Hisashi,

Try this measure:

```PYordercount =
SUMX (
VALUES ( orders[Orderdate] ),
CALCULATE (
DISTINCTCOUNT ( orders[ordernumbers] ),
SAMEPERIODLASTYEAR ( shipment[shippingdate] )
)
)```
• Marked as answer by Monday, July 15, 2019 5:13 AM
Saturday, July 13, 2019 9:42 PM
• Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
With SamePeriodLastYear() and correct SubTotals.
With sum of sales instead of count of orders.
With TimeLine, not list of target dates.
With PivotChart.
Share your full, exact file for customization.
http://www.mediafire.com/file/by92303a7r2mn7f/07_13_19.xlsx/file
http://www.mediafire.com/file/7smzokcke92oews/07_13_19.pdf/file

Saturday, July 13, 2019 10:59 PM
• Hi Aleksei,

Thanks for the code!

This worked perfect. I really appreciate it.

Monday, July 15, 2019 5:13 AM
• Hi Herbert,

Thanks for the tip!

Monday, July 15, 2019 5:16 AM