cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to get previous year with partial periods

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

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 @DaniMak1608 for your post !!!

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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 @DaniMak1608 for your post !!!
Whitewater100
Super User
Super User

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 )

  )

)

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors