Current month minus previous month

[jbenson]

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! 

[jbenson]

i got it to work, thank you for your help!

[marcelsmaglhaes]

Hey @jbenson ! 

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

Best Regards,
Marcel

[jbenson]

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 · Accepted solution]

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

[Swamy3105]

@marcelsmaglhaes 

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

please help me on this.

Regards.,

Swamy

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Swamy3105]

@Ashish_Mathur 

thanks for the help, but I was able to get the previous month data using calculated column and done the report.

Regards.,

Swamy

[Swamy3105]

@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. lavisam0518@gmail.com

Regards.,

Swamy

[marcelsmaglhaes]

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