Forum Discussion

vivek_babu's avatar
vivek_babu
Helper II
1 year ago
Solved

Dax logic help

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_pe...
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    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

     

     

     

     

     

  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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