Forum Discussion

jbenson's avatar
jbenson
Helper III
3 years ago
Solved

Current month minus previous month

i am wanting to add a column to my table. i want to be able to subtract the current months forecast number minus the previous months forecast number. i have a month column also in the same table. i have tried a few different formulas and i always get an error. any help would be great! 

Need Help
Show and Tell
  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    3 years ago

    Hey jbenson 

    Lets try something more simple. To do that, you need to have a calendar table (you can create one using DAX if you don't have one).

    Relate the calendar table with your Grain Margin table. After that, create two measures and that's it. See images bellow:

    Best Regards,
    Marcel

9 Replies

    • marcelsmaglhaes's avatar
      marcelsmaglhaes
      Super User
      3 years ago

      Hey jbenson ! 

      Nice!!!! If this posts helps, please mark as solved to help other in our community.

      Best Regards,
      Marcel

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Super User
    3 years ago

    Hey jbenson 

    Maybe you can try something like bellow:

    Forecast Difference =
    VAR CurrentMonth = 'Table'[Month] 
    VAR PreviousMonth =
    CALCULATE ( MAX ( 'Table'[Month] ), 'Table'[Month] < CurrentMonth )
    RETURN
    IF (
    ISBLANK ( PreviousMonth ),
    BLANK (),
    'Table'[Forecast Number]
    - CALCULATE ( MAX ( 'Table'[Forecast Number] ), 'Table'[Month] = PreviousMonth )
    )


    Best Regards,
    Marcel

  • jbenson's avatar
    jbenson
    Helper III
    3 years ago

    Hey Marcel I appriciate the quick response, here is what i used for a formula 

     

    FORECAST DIFFERENCE = VAR CurrentMonth = 'Grain Margin'[MONTH]
    VAR PreviousMonth =
    CALCULATE(MAX('Grain Margin'[MONTH]), 'Grain Margin'[MONTH] <CurrentMonth)
    RETURN
    IF(ISBLANK(PREVIOUSMONTH),
    BLANK(),
    'Grain Margin'[FORECAST]
    -CALCULATE(MAX('Grain Margin'[FORECAST]),'Grain Margin'[MONTH]=PREVIOUSMONTH))
     
    here is the error i got
    The syntax for 'PreviousMonth' is incorrect. (DAX(VAR CurrentMonth = 'Grain Margin'[MONTH]VAR PreviousMonth =CALCULATE(MAX('Grain Margin'[MONTH]), 'Grain Margin'[MONTH] <CurrentMonth)RETURNIF(ISBLANK(PREVIOUSMONTH),BLANK(),'Grain Margin'[FORECAST]-CALCULATE(MAX('Grain Margin'[FORECAST]),'Grain Margin'[MONTH]=PREVIOUSMONTH)))).
     
     
    • marcelsmaglhaes's avatar
      marcelsmaglhaes
      Super User
      3 years ago

      Hey jbenson 

      Lets try something more simple. To do that, you need to have a calendar table (you can create one using DAX if you don't have one).

      Relate the calendar table with your Grain Margin table. After that, create two measures and that's it. See images bellow:

      Best Regards,
      Marcel

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        marcelsmaglhaes 

         

        can you please send me the sample PBI file to me. I'm struggling to get the exact result for the past one week. [email protected]

         

        Regards.,

        Swamy