Comparing YTD to Previous Year

[RobKay12]

Hello,

I am looking for a way to see data collected for the same time period the previous year. For example I have data from Jan 1 to May 31, 2024 (as seen below) and I want to look up in my table and show the data for only the same time period as what is reported in the 2024 column. I've been trying the "SamePeriodLastYear" function but with no success.

Any recommendations?

Thanks

Site	YTD 2024 Events	2023
Plant A	24
Plant B	12
Plant C	20
Plant D	22

[Anonymous · Accepted solution]

Hi @RobKay12 ,

Based on your description, I created these data.

Create MEASURE.

MEASURE = 
VAR _max_date =
    CALCULATE ( MAX ( '2024'[date] ), ALL ( '2024' ) )
VAR _min_date =
    CALCULATE ( MIN ( '2024'[date] ), ALL ( '2024' ) )
VAR _last_year_start =
    DATE ( YEAR ( _min_date ) - 1, MONTH ( _min_date ), DAY ( _min_date ) )
VAR _last_year_end =
    DATE ( YEAR ( _max_date ) - 1, MONTH ( _max_date ), DAY ( _max_date ) )
VAR _sum =
    CALCULATE (
        SUM ( '2023'[event] ),
        FILTER (
            ALL ( '2023' ),
            '2023'[date] >= _last_year_start
                && '2023'[date] <= _last_year_end
                && '2023'[site] = MAX ( '2024'[site] )
        )
    )
RETURN
    _sum

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RobKay12]

Sorry, I know I didn't explain my probem very good. Bascially I have two tables. 1 (titled 2024 Event Data) is with event data in it for YTD 2024 (Jan 1 to June 30) for each plant, and Table 2 (titled 2023 Event Data) has event data in it from Jan 1 2023 to December 31 2023 for each plant. I want to build a matrix visualization showing the event data for YTD in 2024 and show the numbers of events for each plant for the same time period the previous year. See Table I would like to show for example:

Site	YTD 2024 Events	YTD 2023 Events
Plant A	24	22
Plant B	12	13
Plant C	20	19
Plant D	22	17

I have been trying to use the "SAMEPERIODLASTYEAR" function with a date calendar but is only returning events for all 2023. I am using:

Previous Year = CALCULATE(COUNT('2023 Event Data'[id]), SAMEPERIODLASTYEAR('Calendar'[Date]))

[RobKay12]

[Anonymous]

Hi @RobKay12 ,

You can use the SAMEPERIODLASTYEAR function. It will return a table containing a column of dates that have been moved back one year in the current context from the date in the specified date column. You can refer to the documentation: SAMEPERIODLASTYEAR function (DAX) - DAX | Microsoft Learn

Create MEASURE.

Measure = 
CALCULATE(SUM(financials[ Sales]),SAMEPERIODLASTYEAR('financials'[Date]))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.