Last Year YTD - SamePeriodLastYear returning blank

[Anonymous]

Hello. I'm working on calculating last year YTD, and I'm running into an issue with it not behaving the way I think it should. I've looked at a lot different posts, and tried a LOT of different things.

Here are my calculations:

YTDBillable = CALCULATE (
[Billable],
DATESYTD (
PracticeHours[StartOfMonth]
,"01/31")
)

YTDBillablePY = CALCULATE (
[YTDBillable], SAMEPERIODLASTYEAR(PracticeHours[StartOfMonth]), ALL(PracticeHours)
)

YTDBillablePY2 = CALCULATE (
[YTDBillable], SAMEPERIODLASTYEAR(PracticeHours[StartOfMonth])
)

And here is what's happening: It's working as expected at a high-level, but when I drill down, the YTDBillablePY does not slice by the dimension. Please note that when I REMOVE the ALL(PracticeHours) from the formula (which I did in YTDBillablePY2), it returns blank at the detail level.

Please let me know if I can give you any more information.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[AlexisOlson]

The time intelligence functions like DATESYTD and SAMEPERIODLASTYEAR often don't work properly unless you are using them on a dedicated date table that contains every day (not just StartOfMonth).

If you don't have a date/calendar table, I highly recommend creating one.

[Anonymous]

I do have one, and I tried that as well. Same result. Are there other formulas I can be using to get the same result? Again -- the YTD works, just not the Prior Year YTD.

[AlB]

@Anonymous

If you can provide the pbix it would probably be easier for people to help

[Anonymous]

Ok, thank you. Here is a link to the file. Data's been changed, but you'll get the idea.

https://www.dropbox.com/s/0fb2kr0iu8nuzdl/YTD%20PY%20Issue.pbix?dl=0

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@AlB, @AlexisOlson -- any ideas?

[AlexisOlson]

When you write ALL(PracticeHours) in your YTDBillablePY measure, it removes all filter contexts from that table. You only want to remove the date filter context, so try substituting ALL(Hours) instead.

[Anonymous]

@AlexisOlson, that did not work.

[AlB]

@Anonymous

You have some tricky stuff here. I'm a bit confused in trying to follow the table organisation and the rationale behind what fields are being used for filtering and their location in the tables.

Help me understand your reasoning. What is the ALL( PracticeHours) for??

[AlB · Accepted solution]

@Anonymous

Try this. It seems to work but check it out. You're in a better position to confirm it.

All I've done is added the VALUES( ). Since that's what you are filtering on the lowest level, let's just put it back. But I don't completely understand what is going on. I suspect it has to do with having the field you are using for date filtering (PracticeHours[StartOfMonth]) in your fact table rather than on a Date table. That is dangerous. My guess is that if you had that field in the Date table for filtering, you wouldn't run into this problem. In fact I am kinda surprised YTDBillablePY and YTDBillable work at all but well I do not know much about the inner workings of the time intelligence functions.

OK, more tomorrow...    

YTDBillablePY_B =
CALCULATE (
    [Billable];
    DATESYTD ( SAMEPERIODLASTYEAR ( PracticeHours[StartOfMonth] ); "11/30" );
    ALL ( PracticeHours );
    VALUES ( PracticeHours[StaffLevel] )
)

[Anonymous]

Thank you. So. Much.

I will admit it may have gotten messy in my attempt to try to find the solution.

What you're saying is that I should be joining to a date field on the Start of Month table, and then use that in my Date Filtering calculations? I tried that, and was not able to come up with something that worked.

If anyone has something that is more streamlined in structure, and the formula looks nicer / is simpler, I'm all ears.

[Ashish_Mathur]

Hi,

Whom are you replying to?  Did my solution work?

[AlB]

@Ashish_Mathur

Would you want to explain what your proposed solution does exactly? Thanks

[AlB]

@Anonymous

What was your rationale for using the ALL( PracticeHours)? Just curious

[Anonymous]

Oh, and my rationale for using All(PracticeHours)? Desperation and a lack of understanding the functions. It was getting me closer to what I wanted than not using it.

[Anonymous]

@Ashish_Mathur, I went back and reviewed your solution, and I was too quick to mark the best answer. Your solution worked, and was the simpler solution we were all looking for. Honestly, it was what I was trying all along, and I'm not sure what you did differently than I did, but it is working.

@AlB, here is his calculation: 

YTDBillable = if(ISBLANK([Billable]),BLANK(),CALCULATE (

[Billable],

DATESYTD (

DateDimension[Date]

,"11/30")

))

YTDBillablePY = CALCULATE([YTDBillable],SAMEPERIODLASTYEAR(DateDimension[Date]))

Thank you both for the help. I will make sure to do all of my calculations with a straight date table in the future, as I think you were both right in telling me that was causing some of the formulas to act outside of normal.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Anonymous]

FYI -- my Billable Calculation is: 

Billable = SUMX(PracticeHours,PracticeHours[BillableHours])