Forum Discussion
Helper IComparing YTD to Previous Year
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 |
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 _sumIf 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.
4 Replies
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.
RobKay12Sorry, 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
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 _sumIf 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.