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