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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.