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

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