Forum Discussion

SteveCarter1's avatar
SteveCarter1
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Need 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...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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/434008

     

    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. 

  • OwenAuger's avatar
    OwenAuger
    6 years ago

    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's avatar
SteveCarter1
Icon for Advocate II rankAdvocate II
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago
"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's avatar
    SteveCarter1
    Icon for Advocate II rankAdvocate II
    6 years ago

    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.