Forum Discussion

jbenson's avatar
jbenson
Icon for Helper III rankHelper 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 h...
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

jbenson's avatar
jbenson
Icon for Helper III rankHelper 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
    Icon for Super User rankSuper 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      marcelsmaglhaes 

       

      the below is my sample data, i want to calculate the previous month number.

       

       

      please help me on this.

       

      Regards.,

      Swamy