Transform Data - Zero out December, add to next January

[ScottyR]

I have monthly data as follows...

Year	Month	Budget
2020	11	100
2020	12	-50
2021	1	150
2021	2	100
2021	3	100

I'd like to transform this data or create a custom column that will zero out the December value and add it to the January value in the following month. It would look like this.

Year	Month	Budget	NewBudget
2020	11	100	100
2020	12	-50	0
2021	1	150	100
2021	2	100	100
2021	3	100	100

Any ideas?

[v-yangliu-msft]

HelloScottyR,

According to your description, I added some data to show:

1. Create a column

New Budget =

SWITCH(

'Table'[Month],

12,

IF(

'Table'[Project ID]=CALCULATE(MAX('Table'[Project ID]),FILTER('Table','Table'[Year]=EARLIER('Table'[Year])+1&&'Table'[Month]=1)),0,'Table'[Budget]),

1,

IF(

'Table'[Project ID]=CALCULATE(MAX('Table'[Project ID]),FILTER('Table','Table'[Year]=EARLIER('Table'[Year])-1&&'Table'[Month]=12)),

CALCULATE(

MAX('Table'[Budget]),

FILTER('Table','Table'[Year]=EARLIER('Table'[Year])-1&&'Table'[Month]=12))+'Table'[Budget],

'Table'[Budget]),

'Table'[Budget])

2. Result

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@ScottyR ,

Create a date column and join that with date table

New column

Date  = Date([year],[month],1)

measures

LMTD = CALCULATE(SUM(Table[Budget]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

new budget =

Switch( True() ,

sum(Table[Budget)<0 , 0 ,

[LMT] <0 , [LMTD]+ sum(Table[Budget),

sum(Table[Budget)

)

[v-yangliu-msft]

Hi  @ScottyR,

Here are the steps you can follow：

1. Create a calculated column

New Budget =
IF(
    'Table'[Month]=12,
    0,
    IF(
        'Table'[Month]=1,
        CALCULATE(
            MAX('Table'[Budget]),         FILTER('Table','Table'[Year]=EARLIER('Table'[Year])-1&&'Table'[Month]=12))+'Table'[Budget],
        'Table'[Budget])
)

2. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ScottyR]

@v-yangliu-msft 

This is close. I neglected to mention that my data set includes multiple projects. I have a column called Project_ID. I need to do this separately for each Project ID. The example you provided calculates January values across every Project_ID.

So it looks like this

Project ID	Year	Month	Budget	NewBudget
1	2020	11	100	100
1	2020	12	-50	0
1	2021	1	150	100
1	2021	2	100	100
1	2021	3	100	100
2	2020	11	10	10
2	2020	12	-5	0
2	2021	1	15	10
2	2021	2	10	10
2	2021	3	10	10