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