Forum Discussion

Upali63's avatar
Upali63
Helper II
2 years ago

Create New Column with Data from Same table

Hi Friends   I have a data table as per sample below where the values in Amount Column alwayas show on accumulated basis as at end of each month. Now I want to create a new coloum subtracting curre...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Upali63 ,

I'm a little confused about your needs, Could you please explain them further? 

What is the logic for calculating your desired result, please explain it.

Thanks for your efforts & time in advance.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Upali63's avatar
    Upali63
    Helper II
    2 years ago

    Hi CST,

    Data in my table are recorded in such a way that at the end of each month Amount shown is the cumulative value as at end of month. Eg as 31-Jan-22 the Values for that particular month "Petrol 92  121,257,402.15 " is for the month Jan 22. However when It comes as at 28-Feb-2022 Amount shown is 133,499,144 (Jan+Feb) . So I want to subtract from Feb figure (133,499,144) the value for Jan (121,257,402.15) and need to get the result 12,241,741.85 . That is the amount I need to be shown in Expected New Column. Here I need this subtraction to be done against each Item in Acc_Description column Please. Hope I have clarified it and look forward to your help please.

  • Upali63's avatar
    Upali63
    Helper II
    2 years ago

    Hi CST

    In my data table Values are recorded in a such a way that as at end of each month end Cumulative values are shown in the Amount Column. Eg as at 31-01-22 Value for Petrol 92 (121,257,402.15) is for the month of Jan-22 whereas as at 28-02-22 the value for Petrol-92 (133,449,144)is is Jan and Feb Total Value. Now I want to subtract Jan 22 figure from Feb figure (133,449,144-121,257,402.15=12,242,741.85) and record the same in Expected New Column. The needs to be done based on month end date as well as Item shown (eg Petrol 92) Ac_Description Column pls. I hope I have clarified the issue and look forward to your help please.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Upali63 ,

      Please try below steps:

      1. below is my test tabel

      2. create a new column with below dax formula

      Column =
VAR cur_date = [Date]
VAR next_date =
    EDATE ( cur_date, 1 )
VAR cur_acc = [Acc_description]
VAR cur_amt = [Amount]
VAR next_val =
    CALCULATE (
        MAX ( [Amount] ),
        FILTER ( ALL ( 'Table' ), [Date] = next_date && [Acc_description] = cur_acc )
    )
RETURN
    IF ( ISBLANK ( next_val ), cur_amt, next_val - cur_amt )

       

      Please refer the attached .pbix file

       

      Best regards,
      Community Support Team_Binbin Yu
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Upali63's avatar
        Upali63
        Helper II
        2 years ago

        Thanks for you reply. Here I too have made a mistake

        My desired result should be like below pls

         

        Kindly help me