Forum Discussion
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 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
19 Replies
Appreciate if you help me please
Try something like:
SUM( Value ) - CALCULATE( SUM( Value ) , PARALLELPERIOD( 'Dates'[Date] , 1 , MONTH ) )Thanks for your help
But I get the below result when I added a column as you said
Pls correct me
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.
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)
)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.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.
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.
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.
