Subtract one column value to each column values in same row

ShyamS — Sat, 18 May 2024 16:51:12 GMT

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:

please help to guide me how to achieve this.

Thanks in advance for the time and guidance

Re: Subtract one column value to each column values in same row

Anonymous — Mon, 20 May 2024 07:40:22 GMT

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!

topic Re: Subtract one column value to each column values in same row in DAX Commands and Tips

Subtract one column value to each column values in same row

Re: Subtract one column value to each column values in same row