Dax logic help

[vivek_babu]

Hi All,

I have a scenario where user selects year and month from the slicer and based on the selection i need to calculate delta %. Delta % means it needs to take the average of the compliance_percent column for the selected year and month then it needs to subtract with the average of the compliance_percent column for the previous year and month. Once subtracted then we need to divide and find the Delta %. 

Sample Data,

Date	YrMonth	Compliance_Percent
11/1/2022	2022Nov	0
11/1/2022	2022Nov	13.87
10/1/2022	2022Oct	0
10/1/2022	2022Oct	13.93

Expected output will be 0.06

@lbendlin @rajendraongole1 @danextian @shafiz_p @carlosbarboza @Ritaf1983 @Anonymous @Bibiano_Geraldo @Kedar_Pande @Anonymous @Greg_Deckler @vojtechsima @FreemanZ @DataNinja777 @amitchandak 

Regards

Vivek N

[Kedar_Pande · Accepted solution]

@vivek_babu 

DAX Formula

Delta % =
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR SelectedMonth = SELECTEDVALUE('DateTable'[Month])
VAR CurrentMonthAvg =
CALCULATE(
AVERAGE('Table'[Compliance_Percent]),
'DateTable'[Year] = SelectedYear,
'DateTable'[Month] = SelectedMonth
)
VAR PreviousMonthAvg =
CALCULATE(
AVERAGE('Table'[Compliance_Percent]),
'DateTable'[YearMonthKey] = 
IF(
SelectedMonth = 1,
FORMAT(DATE(SelectedYear - 1, 12, 1), "YYYYMM"),
FORMAT(DATE(SelectedYear, SelectedMonth - 1, 1), "YYYYMM")
)
)
RETURN
IF(
NOT(ISBLANK(CurrentMonthAvg)) && NOT(ISBLANK(PreviousMonthAvg)),
DIVIDE(CurrentMonthAvg - PreviousMonthAvg, PreviousMonthAvg, 0),
BLANK()
)

November 2022 average: (0+13.87)/2=6.935

October 2022 average: (0+13.93)/2=6.965

Delta %: 6.935−6.965\ 6.965=  -0.43%

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[vivek_babu]

Hi @shafiz_p @Kedar_Pande @Bibiano_Geraldo 

Thanks all for providing the solutions. All these solutions works fine 

Regards

Vivek N

[shafiz_p · Accepted solution]

Hi @vivek_babu  Try This :

AvgComplianceSelected = 
CALCULATE(
    AVERAGE('DataTable'[Compliance_Percent]),
    FILTER(
        'DataTable',
        'DataTable'[YrMonth] = SELECTEDVALUE('DataTable'[YrMonth])
    )
)

Avg Prev:

AvgCompliancePrevious = 
VAR _SelectedDate = FORMAT(DATEADD('DataTable'[Date], -1, MONTH), "yyyyMMM")

VAR _Result =
CALCULATE(
    AVERAGE('DataTable'[Compliance_Percent]),
    FILTER(
        ALL('DataTable'),
        'DataTable'[YrMonth] = _SelectedDate
    )
)

RETURN _Result

Delta :

DeltaPercent = 
VAR SelectedAvg = [AvgComplianceSelected]
VAR PreviousAvg = [AvgCompliancePrevious]
RETURN
IF(
    NOT ISBLANK(SelectedAvg) && NOT ISBLANK(PreviousAvg),
    (SelectedAvg - PreviousAvg) / PreviousAvg,
    BLANK()
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[Kedar_Pande · Accepted solution]

@vivek_babu 

DAX Formula

Delta % =
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR SelectedMonth = SELECTEDVALUE('DateTable'[Month])
VAR CurrentMonthAvg =
CALCULATE(
AVERAGE('Table'[Compliance_Percent]),
'DateTable'[Year] = SelectedYear,
'DateTable'[Month] = SelectedMonth
)
VAR PreviousMonthAvg =
CALCULATE(
AVERAGE('Table'[Compliance_Percent]),
'DateTable'[YearMonthKey] = 
IF(
SelectedMonth = 1,
FORMAT(DATE(SelectedYear - 1, 12, 1), "YYYYMM"),
FORMAT(DATE(SelectedYear, SelectedMonth - 1, 1), "YYYYMM")
)
)
RETURN
IF(
NOT(ISBLANK(CurrentMonthAvg)) && NOT(ISBLANK(PreviousMonthAvg)),
DIVIDE(CurrentMonthAvg - PreviousMonthAvg, PreviousMonthAvg, 0),
BLANK()
)

November 2022 average: (0+13.87)/2=6.935

October 2022 average: (0+13.93)/2=6.965

Delta %: 6.935−6.965\ 6.965=  -0.43%

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Bibiano_Geraldo]

Hi @vivek_babu ,

Based on your explanation, the expected output is 0.06. When I analyze your data, it seems that this result comes from subtracting 13.93 - 13.87. However, the 13.93 value for Compliance_Percent corresponds to October, so the previous month should be September, not November, according to your data.

Additionally, in your output, the Delta % of 0.06 appears to be a simple subtraction of 13.93 - 13.87, without any division or average. Could you clarify how the calculation should work in this case?

I would appreciate any additional insights you can provide.

Thank you!