TOTALYTD including entire month with SAMEPERIODLASTYEAR

[smpatric]

Hello,

I'm trying to do a YTD comparison between this year and last. I have two formulas below, one for the Current Year and one for the Prior Year. For some reason, the Current Year calculation is working correctly, but when I apply SAMEPERIODLASTYEAR for the Prior Year calculation, the dates for the entire month of January are brought in. Here are my forumlas:

# Deals YTD = TOTALYTD(SUM('Deal Activity'[Count]),'Date'[Date])

And for the Prior Year YTD:

# Deals PYD = CALCULATE([# Deals YTD],SAMEPERIODLASTYEAR('Date'[Date]))

As noted, the second formula is calculating a Total for 1/1/2018-1/31/2018, but I only want it to show 1/1/2018-1/17/2018. Does anyone know why it would bring in the whole month?

Thanks

[v-frfei-msft · Accepted solution]

Hi @smpatric,

If you only want to get the same period of the dates. We can take the following steps.

1. Create a new date table and create relationship between it and the fact table.

date = CALENDAR(MIN('Deal Activity'[date]),MAX('Deal Activity'[date]))

Year = YEAR('date'[Date])

2. Create a measure as below.

Previsou = TOTALYTD(SUM('Deal Activity'[Count]),DATEADD('date'[Date],-365,DAY))

I guess that should be the issue of the way creating date table. So I create a new one here to work on it.

For more details, please check the pbix as attached.

Regards,

Frank

[smpatric]

Thanks Frank. Should have thought of that, but worked like a charm!