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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ShyamS
Frequent Visitor

Subtract one column value to each column values in same row

1000039893.jpg

 Hi All,

As per my business requirements I have to subtract current year (actual) column values to all the other column(MAR_FCST,PLAN, ACTUAL (2023)) values as shown in the matrix visuals. I am able to that for current year means current year actual column value I am able to subtract from current year mar_fcst, and plan and Actual. But not able to subtract current year actual column value to previous year actual column value.

I am getting correct results in the GTN% GS~ 

For year 2024 columns.

Explanation is below:

I want to subtract 2024 ACTUAL column value which is 43.8% to other column that is 

(43.8-43.8) for plan and (42.8-43.8) for mar-fcst and (42.7-43.8) for plan and (40.9-43.8) for 2023 actual. But I am not able to do that for 2023. 

I am using below dax:

1000039894.jpg

 please help to guide me how to achieve this.

 

Thanks in advance for the time and guidance 

1 REPLY 1
Anonymous
Not applicable

Hi @ShyamS ,

 

I can not help you very well by providing DAX syntax and data in the form of screenshots.

For calculations involving this year and last year, it would be better to create a date table with all the dates in the main table.

 

Please try syntax logic like this:

 

MEASURE =
VAR _sameDayLastYear =
    DATE ( YEAR ( MAX ( 'Date'[Date] ) ) - 1, MONTH ( MAX ( 'Date'[Date] ) ), DAY ( MAX ( 'Date'[Date] ) ) )
VAR _lastYear =
    CALCULATE (
        SUM ( Table[ACTUAL] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Year] = _sameDayLastYear )
    )
VAR _actual =
    SUM ( Table[ACTUAL] )
RETURN
    _lastYear - _actual

 

 

please provide me with the pbix file. Remember not to log in to the current account when uploading files to Power Bi Desktop.

 

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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