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_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,

DateYrMonthCompliance_Percent
11/1/20222022Nov0
11/1/20222022Nov13.87
10/1/20222022Oct0
10/1/20222022Oct13.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

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

4 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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!

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    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
    Super User
    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