Reply
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎01-10-2019
Accepted Solution

TOTALYTD including entire month with SAMEPERIODLASTYEAR

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


Accepted Solutions
Community Support Team
Posts: 1,997
Registered: ‎07-10-2018

Re: TOTALYTD including entire month with SAMEPERIODLASTYEAR

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))

Capture.PNG

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

 

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

View solution in original post

Attachment

All Replies
Community Support Team
Posts: 1,997
Registered: ‎07-10-2018

Re: TOTALYTD including entire month with SAMEPERIODLASTYEAR

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))

Capture.PNG

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

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Frequent Visitor
Posts: 2
Registered: ‎01-10-2019

Re: TOTALYTD including entire month with SAMEPERIODLASTYEAR

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