Forum Discussion

DatamirHub's avatar
DatamirHub
Advocate I
1 year ago
Solved

Custom Order mess time intelligent measures

I have this column RECORD_YEAR which is an integer

and I have this measure

_REVENUE_USD_YoY_Growth = 
VAR CurrentYear = MAX(COMPANIES[RECORD_YEAR])

VAR CurrentYearValue =
    CALCULATE(
        MAX(COMPANIES[REVENUE_USD]),
        COMPANIES[RECORD_YEAR] = CurrentYear
    )

VAR PreviousYearValue =
    CALCULATE(
        MAX(COMPANIES[REVENUE_USD]),
        COMPANIES[RECORD_YEAR] = CurrentYear - 1
    )

RETURN
DIVIDE(CurrentYearValue - PreviousYearValue, PreviousYearValue, 0)

 
Everything works perfectly
After that I created a custom order column in Query M for the RECORD_YEAR which just for descending order (RECORD_YEAR * -1)

and then when i change the sort by of the RECORD_YEAR everything gets messed up and after further investigation I found that the PreviousYearValue var always return empty and i tried removing filter record year but nothing worked 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi DatamirHub 

     

    COMPANIES[RECORD_YEAR] = CurrentYear - 1

    The above internally translates to

    FILTER ( ALL ( COMPANIES[RECORD_YEAR] ), COMPANIES[RECORD_YEAR] = CurrentYear - 1 )

    However, when the column wrapped in ALL is sorted by another column, that other column must also be included in ALL as well. Your new measure should be something like below:

    _REVENUE_USD_YoY_Growth = 
VAR CurrentYear =
    MAX ( Companies[RECORD_YEAR] )
VAR CurrentYearValue =
    CALCULATE (
        MAX ( Companies[REVENUE_USD] ),
        FILTER (
            ALL ( Companies[RECORD_YEAR], Companies[Reverse Sort] ),
            Companies[RECORD_YEAR] = CurrentYear
        )
    )
VAR PreviousYearValue =
    CALCULATE (
        MAX ( Companies[REVENUE_USD] ),
        FILTER (
            ALL ( Companies[RECORD_YEAR], Companies[Reverse Sort] ),
            Companies[RECORD_YEAR] = CurrentYear - 1
        )
    )
RETURN
    DIVIDE ( CurrentYearValue - PreviousYearValue, PreviousYearValue, 0 )

     

4 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi DatamirHub 

     

    COMPANIES[RECORD_YEAR] = CurrentYear - 1

    The above internally translates to

    FILTER ( ALL ( COMPANIES[RECORD_YEAR] ), COMPANIES[RECORD_YEAR] = CurrentYear - 1 )

    However, when the column wrapped in ALL is sorted by another column, that other column must also be included in ALL as well. Your new measure should be something like below:

    _REVENUE_USD_YoY_Growth = 
VAR CurrentYear =
    MAX ( Companies[RECORD_YEAR] )
VAR CurrentYearValue =
    CALCULATE (
        MAX ( Companies[REVENUE_USD] ),
        FILTER (
            ALL ( Companies[RECORD_YEAR], Companies[Reverse Sort] ),
            Companies[RECORD_YEAR] = CurrentYear
        )
    )
VAR PreviousYearValue =
    CALCULATE (
        MAX ( Companies[REVENUE_USD] ),
        FILTER (
            ALL ( Companies[RECORD_YEAR], Companies[Reverse Sort] ),
            Companies[RECORD_YEAR] = CurrentYear - 1
        )
    )
RETURN
    DIVIDE ( CurrentYearValue - PreviousYearValue, PreviousYearValue, 0 )

     

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    1 year ago

    Hi DatamirHub,

     

    Try this:

     

    VAR CurrentYear = MAX(COMPANIES[RECORD_YEAR])
    VAR CurrentYearValue =
    CALCULATE(
    MAX(COMPANIES[REVENUE_USD]),
    FILTER(ALL(COMPANIES), COMPANIES[RECORD_YEAR] = CurrentYear)
    )
    VAR PreviousYearValue =
    CALCULATE(
    MAX(COMPANIES[REVENUE_USD]),
    FILTER(ALL(COMPANIES), COMPANIES[RECORD_YEAR] = CurrentYear - 1)
    )
    RETURN
    DIVIDE(CurrentYearValue - PreviousYearValue, PreviousYearValue, 0)

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    1 year ago

    Your measure breaks because sorting RECORD_YEAR by a custom column (like RECORD_YEAR * -1) disrupts row context in visuals. That causes MAX(RECORD_YEAR) to behave unpredictably, so CurrentYear - 1 doesn’t resolve properly.


    Fix it like this:
    VAR CurrentYear = SELECTEDVALUE(COMPANIES[RECORD_YEAR])
    VAR PreviousYearValue =
    CALCULATE(
    MAX(COMPANIES[REVENUE_USD]),
    FILTER(ALL(COMPANIES), COMPANIES[RECORD_YEAR] = CurrentYear - 1)
    )


    Use SELECTEDVALUE and FILTER(ALL(...)) to restore clean context. That’ll work even with custom sort orders.