Power BI DAX

[Megha2498]

Hi

I need a DAX to calculate the variance % for etime data.

I am trying to using some date related DAX but it is not helping me.

The variance I am trying to get is

(# of open tickets in previous month - # of open tickets in reporting month) / # of open tickets in previous month

Start of the reporting month is 28 -Feb- 2023

sample data is below:

Month	Open Requests (Reporting Month)	Open Request (Previous Month)	Variance%
Feb-23	10206
Mar-23	10062	10206	1.41%
Apr-23	11336	10062	-12.66%
May-23	17215	11336	-51.86%
Jun-23	17390	17215	-1.02%
Jul-23	20903	17390	-20.20%
Aug-23	20692	20903	1.01%
Sep-23	23140	20692	-11.83%
Oct-23	24764	23140	-7.02%
Nov-23	23598	24764	4.71%
Dec-23	22180	23598	6.01%
Jan-24	22566	22180	-1.74%
Feb-24	24318	22566	-7.76%
Mar-24	21883	24318	10.01%
Apr-24	22906	21883	-4.67%
May-24	18542	22906	19.05%
Jun-24	24589	18542	-32.61%

[barritown · Accepted solution]

Hi @Megha2498,

You can create a calculated column below and adjust the output format to percentage:

In plain text:

calc_column_solution = 
VAR curMonthValue = [Open Requests (Reporting Month)]
VAR curMonth = [Month]
VAR prevMonth = MAXX ( FILTER ( tbl_with_data, [Month] < curMonth ), [Month] )
VAR prevMonthValue = MINX ( FILTER ( tbl_with_data, [Month] = prevMonth ), [Open Requests (Reporting Month)] )
RETURN DIVIDE ( prevMonthValue - curMonthValue, prevMonthValue )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian