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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.