Forum Discussion

ScottyR's avatar
ScottyR
Frequent Visitor
5 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

HelloScottyR,

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

v-yangliu-msft_0-1603186535336.png

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

v-yangliu-msft_1-1603186535346.png

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.