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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RobKay12
Helper I
Helper I

Comparing 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

 

SiteYTD 2024 Events2023
Plant A24 
Plant B12 
Plant C20 
Plant D22 
1 ACCEPTED SOLUTION
v-kaiyue-msft
Community Support
Community Support

Hi @RobKay12 ,

 

Based on your description, I created these data.

vkaiyuemsft_0-1719817230548.png

 

vkaiyuemsft_1-1719817230550.png

 

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

vkaiyuemsft_2-1719817255002.png

 

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.

View solution in original post

4 REPLIES 4
v-kaiyue-msft
Community Support
Community Support

Hi @RobKay12 ,

 

Based on your description, I created these data.

vkaiyuemsft_0-1719817230548.png

 

vkaiyuemsft_1-1719817230550.png

 

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

vkaiyuemsft_2-1719817255002.png

 

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
Helper I
Helper I

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:

SiteYTD 2024 EventsYTD 2023 Events
Plant A2422
Plant B1213
Plant C2019
Plant D2217

 

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_0-1719409179537.png

 

v-kaiyue-msft
Community Support
Community Support

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

vkaiyuemsft_0-1719277897415.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.