Need help with SAMEPERIODLASTYEAR function and a filtered master calendar

[SteveCarter1]

I have a measure like so:

Count of Appointments Same Period Last Year = CALCULATE(COUNTA(Appointments[Status]),SAMEPERIODLASTYEAR('Master Calendar'[Date]))

My data relationships are like this: Master Calendar Period filters Master Calendar filters Appointments

In reporting visualisation if I have the Master Calendar 'Date' column as a slicer, I can get a nice matrix table showing current and previous year appointment numbers like so:

My problem is if I use the Calendar Period table as a date slicer, I no longer get the previous year period numbers, unless I include the previous year in the slicer too:

I understand that this is happening because I am filtering my Master Calendar due to my selection in the Calendar Period table so the measure can't calculate numbers for the last year period.

Right now as a workaround I tell people they need to select a Calendar Period that spans over 1 year in order to display previous years numbers along side but I would like to present the data as in the top table screenshot while still using an easy to choose time period slicer as I already have in the Calendar Period table.

I need to keep the date hierarchy available so users can choose a date, week, month, quarter, year etc and the DAX calculation needs to work with all those potentional groupings, which is why I love the sameperiodlastyear function as it takes care of that for me.

I like the Calendar Period slicer as does management, to quickly pick a time period so I don't want to push onto them the more cumbersome Master Calendar date slicer.

I'm thinking there should be a DAX solution but can't get it.

[Greg_Deckler · Accepted solution]

@SteveCarter1 - There is. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

The basic technique, you figure out the date of last year min and max from the current user selections. You use ALL or ALLSELECTED or REMOVEFILTERS to override your filter context for date and then filter back down to the range you want. @ me if you have trouble implementing and post sample data as text in a table so I can mock up your situation and get you a more specific answer. 

[Anonymous]

Apart from what @OwenAuger says, you could also INCORPORATE the Master Calendar Periods into the main calendar, Master Calendar. Of course, you would then not be able to mark the table as a date table but you would have one table instead of two, which would be more intuitive for the end user. I know there is a 1:* relationship between the tables. THIS DOES NOT PREVENT YOU FROM MERGING THEM.

[amitchandak]

@SteveCarter1 , I am not sure of the role of a master calendar period. But make sure the master calendar is marked as date calendar Right-click on the table. There is an option.

you can also try

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[SteveCarter1]

Thanks for taking the time to reply.

I use the calendar period table to create arbitary relative time frames that can be quickly chosen from the slicer instead of requiring the end user to manually pick out a date range. For example I can create a 'Last 13 Months' period selection that would filter my master calendar to the last 13 months. I know this can be acheived without any modelling using the filter pane and relative date selection but to be frank that is simply too hard for the vast majority of my end users to deal with. Seeing a fixed set of options in a drop down seems to me, based on my client base and feedback, as the most effective solution.

[Anonymous]

"I use the calendar period table to create arbitary relative time frames that can be quickly chosen from the slicer instead of requiring the end user to manually pick out a date range."

Sorry but I don't get it.... How relevant the above is to what I wrote? Because I can't see any link. You can create your relative frames also in one big date table. You don't need 2 tables to do that.

[SteveCarter1]

I need to re-think my date table logic. I was under the impression for a table marked as a date table I needed a sequence of consecutive dates with no duplicates.

For it to be in one big table where I can choose 1 column for my slicer I would have thought I'd need 1 column with the period name in it then that period name I want chosen against each date, but because 1 date can be associated with more than 1 period name - e.g. the dates for 'Last Week' are the same for 'All Time' and 'This Year' and 'Last Year to Date' etc - I would have thought I would need the same date duplicated for each period name.

As an example for 1st Jan 2020 I have this in my Calendar Period table:

I generate that table in M using the master calendar table as a reference then create a new column for each period name and mark each date as a null if it does not apply or 1 if it applies to that period name then I unpivot the table so I end up with 2 columns like you see above.

Setting a relationship between those 2 tables on the date then lets me filter my master date table based on the period name.

I can't see how I can achieve this in a single master date table with a set of unique dates.

I'll be sure to look further into it though.

Thanks for the feedback.

[OwenAuger · Accepted solution]

@SteveCarter1  I would suggest adding REMOVEFILTERS ( 'Master Calendar Periods' ) within CALCULATE (can use ALL in place of REMOVEFILTERS if you like).

Your selections on 'Master Calendar' / 'Master Calendar Periods' still determine the initial dates that are visible in the visual, but the "Last Year" measure is no longer constrained by any filters from 'Master Calendar Periods'.

Oh, and your 'Master Calendar' table should be marked as a Date Table if it isn't already.

Count of Appointments Same Period Last Year =
CALCULATE (
    COUNTA ( Appointments[Status] ),
    SAMEPERIODLASTYEAR ( 'Master Calendar'[Date] ),
    REMOVEFILTERS ( 'Master Calendar Periods' )
)

 It worked in a test model at my end. Does it work for you?

Regards,

Owen

[SteveCarter1]

Yours is by far the most succint and correct answer. Thank you.

I think I was having a 'can't see the forest amonst the trees' moment.

[Greg_Deckler · Accepted solution]

@SteveCarter1 - There is. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

The basic technique, you figure out the date of last year min and max from the current user selections. You use ALL or ALLSELECTED or REMOVEFILTERS to override your filter context for date and then filter back down to the range you want. @ me if you have trouble implementing and post sample data as text in a table so I can mock up your situation and get you a more specific answer. 

[SteveCarter1]

As usual Greg, an extremely comprehensive reply and anyone coming across this thread can only be wiser for clicking through to read your blog article.