Forum Discussion

ShyamS's avatar
ShyamS
Frequent Visitor
2 years ago
Solved

Subtract one column value to each column values in same row

 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 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ShyamS 

     

    Given that I don't understand the structure of your specific data, I can only give you some suggestions that might be useful:

    Maybe you can change your measure into this:

    GTN % GS ~ =
VAR A = [GTN % GS]
VAR CurrentDate =
    MAX ( CV_GMR_PL_APAC_REPORT[Year] )
VAR B =
    CALCULATE (
        [GTN % GS],
        CV_GMR_PL_APAC_REPORTING[Year] = YEAR ( CurrenDate ),
        CV_GMR_PL_APAC_REPORTING[Scenario] = "ACTUAL"
    )
VAR C = ( ( A - B ) * 10000 )
RETURN
    C

    Hope it helps,

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


     

2 Replies

  • ShyamS's avatar
    ShyamS
    Frequent Visitor
    2 years ago

    Hi Anonymous , 

    Need your guidance on above query.

    Thank you in advance for your help and guidance.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi ShyamS 

       

      Given that I don't understand the structure of your specific data, I can only give you some suggestions that might be useful:

      Maybe you can change your measure into this:

      GTN % GS ~ =
VAR A = [GTN % GS]
VAR CurrentDate =
    MAX ( CV_GMR_PL_APAC_REPORT[Year] )
VAR B =
    CALCULATE (
        [GTN % GS],
        CV_GMR_PL_APAC_REPORTING[Year] = YEAR ( CurrenDate ),
        CV_GMR_PL_APAC_REPORTING[Scenario] = "ACTUAL"
    )
VAR C = ( ( A - B ) * 10000 )
RETURN
    C

      Hope it helps,

       

      Best Regards

      Zhengdong Xu
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.