Need help on Month to Month Cost Gap in %

[arun2001mjl]

Hello Team,

I need your help to find Previousmonth (Jan23) & CurrentMonth(Feb 23) Delta and also Currentmonth (Feb 23) and April 2022 (Baseline) Delta. Iam using the below DAX but it is calculating all Cost.

Current Month

MAX Year CostActual = CALCULATE(SUM('ABC'[Cost]), 'Dim Period'[Year] = MAX('Dim Period'[Year]))

-MAX Year CostActual = CALCULATE(SUM('ABC[Cost]), DATEADD('Dim Period'[Year],-1,MONTH))

MAX Year CostActual shows Feb Month Cost but -MAX Year CostActual taken Total Cost and subtracting the Feb month cost which is not correct.

Below is the Table and Calculations.

	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	delta vs last month	delta vs baseline
Trend	650	636	620	500	700	550	650	666	636	650	666	2.46%	2.40%
Technology1	24453	636	620	500	700	550	650	666	636	89552	55471	-38.06%	55.92%
Technology2	666	666	620	500	700	550	650	666	636	650	590	-9.23%	-12.88%
Technology3	700	700	620	500	700	550	650	666	636	650	666	2.46%	-5.11%
Technology4	590	590	620	500	700	550	650	666	636	650	660	1.54%	10.61%
Technology5	666	666	620	500	700	550	650	666	636	650	666	2.46%	0.00%
Technology6	650	636	620	500	700	550	650	666	636	666	700	5.11%	7.14%
Technology7	666	636	620	500	700	550	650	666	636	700	590	-15.71%	-12.88%
Technology8	700	636	620	500	700	550	650	666	636	590	666	12.88%	-5.11%
Technology9	590	636	620	500	700	550	650	666	636	666	666	0.00%	11.41%
Technology10	666	636	620	500	700	550	650	666	636	700	666	-4.86%	0.00%
Technology11	650	636	620	500	700	550	650	666	636	590	666	12.88%	2.40%
Technology12	650	636	620	500	700	550	650	666	636	666	666	0.00%	2.40%
Technology13	650	636	620	500	700	550	650	666	636	650	666	2.46%	2.40%

1) Delta Vs Last month is calcuated based on (Feb-23 - Jan-23)/Jan-23 (Should be dynamic for everymonth)
2) Delta Vs Baseline is Calculated baesd on (Feb-23 - Apr-22)/Feb-23 (april is baseline here - Static)

Could you please help with this calculation in PowerBI.

[lbendlin · Accepted solution]

- you show too much data (only Apr 22, Jan 23 and Feb 23 are required)

- the data is not in a usable format - please unpivot the month columns

- you  don't seem to be using a proper calendar table. Time intelligence functions like DATEADD require a contiguous and covering date column.

- don't use MAX inside of CALCULATE  unless you intend to modify the filter context like that. Better use variables.