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
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
v-huijiey-msft
Community Support
Community Support

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
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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.