Dax logic help

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

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