Create New Column with Data from Same table

[Upali63]

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 current month value against the same item from the Amount  in the next month. Therefore please help me to achieve this since I am new to power bi

[v-binbinyu-msft]

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]

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.

[v-binbinyu-msft]

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]

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

My desired result should be like below pls

Kindly help me

[Upali63]

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.

[pradeep_kare12]

Open your Power BI Desktop file.In the Fields pane on the right-hand side, select your data table.In the Modeling tab, click on "New Column."In the formula bar that appears at the top, you can use the DAX formula to create the new column. You can use the LAG function to retrieve the value from the next month and subtract it from the current month's value. Here's an example formula :

NewColumn = YourTable[Amount] -
CALCULATE(SUM(YourTable[Amount]),
FILTER(YourTable, YourTable[Month] = EARLIER(YourTable[Month]) + 1)
)

[Upali63]

Thanks for Your help

I did as you said and below is the result pls. Kindly look into that help me

[Upali63]

Hi pradeep,

Could you have a look at my issue and help me please.

[CoreyP]

Try something like:
SUM( Value ) - CALCULATE( SUM( Value ) , PARALLELPERIOD( 'Dates'[Date] , 1 , MONTH ) )

[Upali63]

Thanks for your help

But I get the below result when I added a column as you said

Pls correct me

[CoreyP]

Oh, you created that as a calculated column. It needs to be a measure. Additionally, you need to set up a date dimension table in your model. The 'Dates'[Date] segment of my DAX function references the date table you need to set up. 

[Upali63]

Thanks

Did As you advised and below is the result

[CoreyP]

Can you share a screenshot of the relationships in your model view? 

[Upali63]

[CoreyP]

For some reason this seems to work:

[Upali63]

It is working Thanks lot

It works only if I repalce Calender[Date] with Table (TB) [date]

If you dont mind can you help me to achieve same thing with a calculated column please

[CoreyP]

Ohhh, I see what I did wrong. It's aggregating only for the date granularity, and not taking into account the account type. 

[Upali63]

Could you then share the correct measure that I should write  please. Thanks a lot

[Upali63]

Appreciate if you help me please