Forum Discussion

ScottyR's avatar
ScottyR
Frequent Visitor
6 years ago

Transform Data - Zero out December, add to next January

I have monthly data as follows...

 

YearMonthBudget
202011100
202012-50
20211150
20212100
20213100

 

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.

 

YearMonthBudgetNewBudget
202011100100
202012-500
20211150100
20212100100
20213100100

 

Any ideas?

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      ScottyR
      Frequent Visitor
      6 years ago

      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 IDYearMonthBudgetNewBudget
      1202011100100
      1202012-500
      120211150100
      120212100100
      120213100100
       22020111010
       2202012-50
       2202111510
       2202121010
       2202131010

       

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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)

    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 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.