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 )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
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 )