Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Move Calculated Column value ahead in time?

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 li...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    Anonymous,

     

    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] )
        )
)
ryan_mayu's avatar
ryan_mayu
Super User
7 years ago

Anonymous

 

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))

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-chuncz-msft
      Community Support
      7 years ago

      Anonymous,

       

      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] )
        )
)