Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors