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
Bc0303
Frequent Visitor

Percentage Increase Prior Year vs This Year

Hi,

Im trying to add a column to my Matrix table which will show the Percentage increase/decrease of Volume from Last year to Current Year (this will be effected by filter so if I select 2024 it will compare 2023 to 2024 and so on the Matrix

 

My data looks like this in power BI, the Volumes are what I want to show but on the Matrix table I have it has a list of Activities A-H a few other measures that are summed by the activity and year (based on the filter on the page) then I want to have a column that says Previous Year Volume which will be a sum of Activity A for lets say 2022 then another Column that shows Sum of A for 2023 then a percentage of the increase or decrease so 50% increase or 30% decrease. THANKS in advance for the help

 

DateActivitiesVolumesChecksDSCost
2022A34001455
2022B40050356
2022C2221226636
2022D2154016564
2022E52440336
2022F458603785
2022G1564023456
2022H1598701636
2023A156124267
2023B321606547
2023C654126873
2023D346524333
2023E32160422
2023F32102515
2023G32440126
2023H13525027
2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1713583557894.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

v-yaningy-msft
Community Support
Community Support

Hi, @Bc0303 

You can refer to @Ashish_Mathur  and @lbendlin reply, if they don't meet your needs, you can try using the following measures to solve your problems.

vyaningymsft_2-1713755168650.png

Measure:

 

 

Changes in Volumes = 
VAR _slicerYear =
    SELECTEDVALUE ( 'Year'[Date] )
VAR _tableYear =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _preVolumes =
    CALCULATE (
        MAX ( 'Table'[Volumes] ),
        FILTER ( 'Table', 'Table'[Date] = _slicerYear - 1 )
    )
VAR _curVolumes =
    CALCULATE (
        MAX ( 'Table'[Volumes] ),
        FILTER ( 'Table', 'Table'[Date] = _slicerYear )
    )
VAR _diff = _curVolumes - _preVolumes
VAR _result =
    DIVIDE ( _diff, _preVolumes )
RETURN
    _result



Previous Year = 
VAR _slicerYear =
    SELECTEDVALUE ( 'Year'[Date] )
VAR _result = _slicerYear - 1
RETURN
    _result

 

 

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

3 REPLIES 3
v-yaningy-msft
Community Support
Community Support

Hi, @Bc0303 

You can refer to @Ashish_Mathur  and @lbendlin reply, if they don't meet your needs, you can try using the following measures to solve your problems.

vyaningymsft_2-1713755168650.png

Measure:

 

 

Changes in Volumes = 
VAR _slicerYear =
    SELECTEDVALUE ( 'Year'[Date] )
VAR _tableYear =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _preVolumes =
    CALCULATE (
        MAX ( 'Table'[Volumes] ),
        FILTER ( 'Table', 'Table'[Date] = _slicerYear - 1 )
    )
VAR _curVolumes =
    CALCULATE (
        MAX ( 'Table'[Volumes] ),
        FILTER ( 'Table', 'Table'[Date] = _slicerYear )
    )
VAR _diff = _curVolumes - _preVolumes
VAR _result =
    DIVIDE ( _diff, _preVolumes )
RETURN
    _result



Previous Year = 
VAR _slicerYear =
    SELECTEDVALUE ( 'Year'[Date] )
VAR _result = _slicerYear - 1
RETURN
    _result

 

 

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1713583557894.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

Do you want this as a measure or a visual calculation?

 

lbendlin_0-1713573506867.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.