Move Calculated Column value ahead in time?

[Kote101]

Hi guys, i need help moving my calculated column calculation value (can be a measured if needed) into month ahead of it. The image below should make things clear.

Instead of having my target line (the red line) show up as 1 (the calculated value ) on April 2019, i would like it to copy the value of the planned line for April 2019 and replace the planed value (5) on march 2019 to 1 (the calculated value). The idea is as i input data, the calculation portion of my dax will run for the following month.

Target = 

IF(OR(NOT(ISNUMBER('Data Table'[Actual])),'Data Table'[Planned]='Data Table'[Actual]),'Data Table'[Planned],DIVIDE('Data Table'[Planned]-'Data Table'[Actual],'Data Table'[Planned]) *'Data Table'[Planned] + CALCULATE(SUM('Data Table'[Planned]),NEXTMONTH('Data Table'[Fiscal Dates])))

Heres my calculated column. The if statement allows me to emulate the planned values so that my target line is equal planned values except when my input data goes above or below my planned values. If this is the case the second part of the if statement instigates a calculation that alters the amount i would need in the following month to keep me on track of my plan.

Heres a link to my pbix and excel file : https://filebin.net/qlgusdv2f3w0pfm4

[ryan_mayu]

@Kote101

I only see 0 value for Mar and Apr in your example. Could you please let me know what is the result you expected. You can just give a simple example in excel file.

Are you trying to move the target value one month earlier?

Column = CALCULATE(SUM('Data Table'[Target]),DATEADD('Data Table'[Fiscal Dates],1,MONTH))

[Kote101]

@ryan_mayu, thank you for your response, i have created a new file in this link; https://filebin.net/qlgusdv2f3w0pfm4,  called test data example.

In this excel file you can see an example of how the data should progress as i input in values. For the two starting 0 values in the first two months, if the planned and actual are equal then the target (the dax) should just input 0, and for any dates above the last input value (look in the excel example file ) they should be automatically 0 too. I hope this clears it up.

[v-chuncz-msft · Accepted solution]

@Kote101,

You may refer to the following code.

Column =
IF (
    CALCULATE (
        SUM ( 'Data Table'[Actual] ),
        PREVIOUSMONTH ( 'Data Table'[Fiscal Dates] )
    )
        <> BLANK (),
    'Data Table'[Planned]
        + CALCULATE (
            SUM ( 'Data Table'[Planned] ) - SUM ( 'Data Table'[Actual] ),
            PREVIOUSMONTH ( 'Data Table'[Fiscal Dates] )
        )
)