Forum Discussion

Marshmallow's avatar
Marshmallow
Helper II
11 months ago
Solved

Help with DAX - YOY

Hi  I tried to do count of current year and previous year for my next calculations. Below are the steps i did   Step 1. create the calendar table: Calendar = Var _Calendar = CALENDARAUTO() RET...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    11 months ago

    Hi,

    In the previous year measure, try not to use REMOVEFILTERS DAX function, because it will remove all filters even users select year from year-slicer on the report.
    Instead, try to use ALLSELECTED() DAX function and please check if it works. And then, we can start from there to optimize the DAX.

    REMOVEFILTERS function (DAX) - DAX | Microsoft Learn

     

    ALLSELECTED function (DAX) - DAX | Microsoft Learn

     

    total prev yr =
VAR _currentyear =
    YEAR ( CALCULATE ( MAX ( data[RejectDate] ), ALLSELECTED () ) )
VAR _previousyr =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = _currentyear ),
        'Calendar'[Year]
    )
VAR _count2 =
    CALCULATE ( [Count_NotApprov], 'Calendar'[Year] = _previousyr - 1 )
RETURN
    IF ( [Current Yr NotAppr], _count2 )

     

V-yubandi-msft's avatar
V-yubandi-msft
Community Support
11 months ago

Hi Marshmallow ,

The response Jihwan_Kim , provided earlier matches your requirement. Using ALLSELECTED() keeps the slicer context, so the measure updates dynamically based on user actions. This avoids the fixed behavior from REMOVEFILTERS(), which removes all filters, including slicer choices.

 

Please review the updated logic and let me know if you need more details or help.

 

Thanks,

Yugandhar