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()
RETURN
    ADDCOLUMNS(
        _calendar,
        "Year", Year ([Date]),
        "MonthNumber", Month ([Date]),
        "Month", Format ([Date], "mmmm"),
        "Quarter", "QTR" & FORMAT([Date], "Q"),
        "QarterNumber", FORMAT([Date], "Q"),
        "MonthYearNumber", FORMAT([Date], "yy mm"),
        "Month Year", Format([Date], "mmm yyyy"))
 
 
Step 2: Create measure of "count_notaprov
Count_NotApprov =
CALCULATE(
    COUNTROWS(data),
    data[reason] in {"Rej", "Withdrawn", "Ineligible"}
)
 
Step 3: total selected yr
Current Yr NotAppr =
var _currentyear =
    year(CALCULATE(
        max(data[RejectDate]),
         REMOVEFILTERS() ))

var _currentyear1=
    MAXX(
        filter(All('Calendar'), 'Calendar'[Year] = _currentyear),
        'Calendar'[Year])

var _count =
    CALCULATE(
        [Count_NotApprov],
        'Calendar'[Year] = _currentyear1)

        RETURN
        _count
 
Step 4: total prev yr
var _currentyear =
    year(CALCULATE(
        max(data[RejectDate),
         REMOVEFILTERS() ))

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)
 
when i select Yr 2025 on the yr filter which is using the Year from the calendar created, total both current and prev yr is correct.
however, when i select yr 2024 on the yr filter, total 2024 is correct and total prev yr becomes static.
 
please help...
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 )

     

5 Replies

  • 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 )

     

  • ashi7upt's avatar
    ashi7upt
    Helper I
    11 months ago

    This issue might be due to the calculation of _currentyear. Present your logic, as this calculation year is not from the calendar table but from the data table.

    For the calculation of the previous year count, we can also use the selectedvalue() 

    total prev yr =
VAR _currentyear = SELECTEDVALUE('Calendar'[Year])
VAR _previousyr = _currentyear - 1
VAR _count2 =
    CALCULATE ( [Count_NotApprov], 'Calendar'[Year] = _previousyr - 1 )
RETURN
    IF ( [Current Yr NotAppr], _count2 )
  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    11 months ago

    Hello Marshmallow 

     

    Try these 2 measure

    Current Year Not Approved
    Current Yr NotAppr =
    VAR _currentyear =
    MAX ( 'Calendar'[Year] ) // get the selected year from calendar
    RETURN
    CALCULATE (
    [Count_NotApprov],
    'Calendar'[Year] = _currentyear
    )

    Previous Year Not Approved
    Prev Yr NotAppr =
    VAR _currentyear =
    MAX ( 'Calendar'[Year] )
    RETURN
    CALCULATE (
    [Count_NotApprov],
    'Calendar'[Year] = _currentyear - 1
    )

     


    If my response helped you, please consider clicking
    Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


    Thanks,


    Connect with me on:

    LinkedIn

     

  • danextian's avatar
    danextian
    Super User
    11 months ago

    Hi Marshmallow 

     

    It appears to be a problem with your _currentyear calculation. You're using the one from the fact table instead of from the calendar table.  Change your variable to this:

    CALCUALTE ( MAX ( 'Calendar'[Year] ), ALLSELECTED ( 'Calendar' ) )
  • 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