How to get previous year with partial periods

[Anonymous]

Hi.  I've seen many variations on this but none that actually address my scenario.

I am trying to get the count of tickets from the same dates last year, which may or may not involve partial periods, but no matter what I try (DATEADD, SAMEPERIODLASTYEAR, PARALLELPERIOD), the count I get back is for the entire month of whatever ending date is being used in the date slicer.

Details:

The Date slicer (Between option) defaults to whatever starting date the user selects thru TODAY() (which is when my date table ends).  Thus, the end date is not physically selected by the user, it's just populated automatically.  So if the date range is Feb 1, 22 thru today, what I keep getting back from my PY formula attempts is Feb 1, 21 thru Apr 30, 21 (not Apr 21, 21). 

I saw a comment from @amitchandak in another post that said something about time intelligence formulas requiring the end date to be specifically chosen.  So when an end date is specifically selected, it works fine, but the user shouldn't have to specifically select "today"s date.  I have formulas that extract the start and end dates (see below) but I can't figure out how to pass those dates into a formula using the above options.  I fear I am either overcomplicating it or trying to oversimplify it.

Can someone help me complete the formula or give me a different formula that should work?  Please.

PY Count = 

VAR min_date = CALCULATE(

    MIN('DATE Table'[Date]),

    ALLEXCEPT('DATE Table','DATE Table'[Date])

)

VAR max_date = CALCULATE(

    MAX('DATE Table'[Date]),

    ALLEXCEPT('DATE Table','DATE Table'[Date])

)

RETURN

CALCULATE('Incidents'[Inc Count], what do I put here to get previous year only thru the max_date of the previous year?)

[Anonymous · Accepted solution]

I stumbled across the answer in another post and was able to tweak that formula:

PY Count =

VAR start_of_period = FIRSTDATE(DATEADD('DATE Table'[Date],-1,YEAR))

VAR end_of_period = MAXX('DATE Table',DATEADD('DATE Table'[Date],-1,YEAR))

RETURN

CALCULATE([Overall Count], SAMEPERIODLASTYEAR ( 'DATE Table'[Date]), 'DATE Table'[Date] >= start_of_period && 'DATE Table'[Date] <= end_of_period)

Thank you @Anonymous for your post !!!

[Whitewater100]

Hi:

Can you try this Date Table and see if you have same issue? Need to mark as Date Table (Table Tools Option) and make the relationships. This Date table needs to connect to your fact table on a date field. Then your measures should be fine. For example:

Amount This Year = SUM(FactTable[Sales Amt])

Amt Last Year = CALCULATE([Amount This Year], SAMEPERIODLASTYEAR(Dates[Date]))

This Calendar goes back 2 years . You can change it to be more years by changing the "-2".

NEW TABLE...

DATES =

  GENERATE (

    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),

    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday   

    VAR currentDay = [Date]

    VAR days = DAY( currentDay )

    VAR months = MONTH ( currentDay )

    VAR years = YEAR ( currentDay )

    VAR nowYear = YEAR( TODAY() )

    VAR nowMonth = MONTH( TODAY() )

    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

    VAR todayNum = WEEKDAY( TODAY() )

    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

  RETURN ROW (

    "day", days,

    "month", months,

    "year", years,

    "day index", dayIndex,

    "week index", weekIndex,

    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),

    "year index", INT( years - nowYear )

  )

)