Forum Discussion
Transform Data - Zero out December, add to next January
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 custo...
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.
Anonymous
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
