Forum Discussion
Advocate IINeed help with SAMEPERIODLASTYEAR function and a filtered master calendar
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.
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-TITHW/m-p/434008The 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 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
9 Replies
- Greg_Deckler
Community Champion
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-TITHW/m-p/434008The 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.
SteveCarter1As usual Greg, an extremely comprehensive reply and anyone coming across this thread can only be wiser for clicking through to read your blog article.
- OwenAuger
Super User
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.
- 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-Last-Week/ba-p/1051123- 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.
- "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.
- 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.
