cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## Year over Year Cost Calculation

Hi,

For my report, I am trying to report out on the year over year cost increase, and my source data is in a similar format to the data shown in the table below. I am trying to create a measure that will return the cost for the prior year/month/day etc. that varies based on the table or chart. I've looked around the other posts on this topic, but I can't seem to get them to work. I show a sample dataset then what the output should look like when summing by year and supplier.

Sample Dataset:

 Date Month Year Identifer 1 Supplier Cost 1/1/2021 1/1/2021 2021 1 A 120 1/2/2021 1/1/2021 2021 2 A 110 1/3/2021 1/1/2021 2021 3 B 100 1/4/2021 1/1/2021 2021 4 B 90 1/5/2021 1/1/2021 2021 5 C 80 1/6/2021 1/1/2021 2021 6 C 70 1/1/2020 1/1/2020 2020 1 A 60 1/2/2020 1/1/2020 2020 2 A 50 1/3/2020 1/1/2020 2020 3 B 40

Output:

 Year Supplier Cost Prior Year Cost Trend 2021 A 230 110 120 2021 B 190 40 150 2021 C 150 0 150 2020 A 110 0 110 2020 B 40 0 40 2020 C 0 0 0
1 ACCEPTED SOLUTION
Super User

Try these measures :

``````Prior Year =
VAR _LY =
CALCULATE (
SUM ( 'Table'[Cost] ),
ALLEXCEPT ( 'Table', 'Table'[Supplier] ),
PREVIOUSYEAR ( 'Table'[Date] )
)
RETURN
IF ( ISBLANK ( _LY ), 0, _LY )``````
``````Cost Trend =
SUM ( 'Table'[Cost] ) - [Prior Year]``````

Output:

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

2 REPLIES 2
Regular Visitor

Thank you! That solved my problem. Really appreciate the help.

Super User

Try these measures :

``````Prior Year =
VAR _LY =
CALCULATE (
SUM ( 'Table'[Cost] ),
ALLEXCEPT ( 'Table', 'Table'[Supplier] ),
PREVIOUSYEAR ( 'Table'[Date] )
)
RETURN
IF ( ISBLANK ( _LY ), 0, _LY )``````
``````Cost Trend =
SUM ( 'Table'[Cost] ) - [Prior Year]``````

Output:

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors