Help convert NONBLANK to non-alphabetical for text values

[Anonymous]

Hello, I have the following matrix visual:

Table name: Spend by Month-Year

First column: Month-Year

Second column: Spend (in $)

I also have a slicer for Month-Year (where users can select any month-year and see how the spend changes each month)

(Note: the slicer was originally displaying the month-year in alphabetical order (i.e. April 2024, August 2024, etc.). To fix this, I created a column where each month-year is assigned a different number (i.e. January 2024 = 1, February 2024 = 2, March 2024 = 3, etc.) and then I sorted my slicer by the new column). 

(Note 2: I had to make the slicer as "text" format because my end user wanted to be able to use the search box in the slicer, and added the search box into a slicer is only doable when the format is text). 

Now onto the issue. My end user wants to be able to click any two dates in the slicer and see the calculated percent change of the spend. They want this percent change to be dynamic based on whichever two values in the slicer are selected). 

I've successfuly done this with the following formula:

Percent change of spend =

VAR startdate =

    LASTNONBLANK('Table_Month'[Month-Year],1)

VAR enddate =

    FIRSTNONBLANK('Table_Month'[Month-Year],1)

RETURN

    IF(ISFILTERED('Table_Month'[Month-Year]),(DIVIDE (

        CALCULATE ( [sumspend], enddate ),

        CALCULATE ( [sumspend], startdate )

    ) - 1),0)

However, the percent change is getting jumbled up when I try to select a month in 2024 vs. a month in 2025. For example, if I choose "December 2024" and "January 2025" on the slicer, I get -5% when it should actually be +5%. So the percentage is correct but it seems like the +/- is incorrect. 

I believe it's doing this because in the calendar year, January is the first month of the year and December is the 12th-- so it's assuming January 2025 occured EARLIER than December 2024. 

I just want to be able to select any date in 2024 and any date in 2025 and get the correct + or -. My formula is working 100% for any dates that are both selected in 2024, but once I click on 2025, the + and - get swapped. 

Please help if possible! Thank you very much

[Bibiano_Geraldo]

Hi @Anonymous ,

To achieve your goal use the bellow DAX:

Percent change of spend =
VAR StartMonthYear =
    CALCULATE(
        MIN('Table_Month'[Month-Year]),
        ALLSELECTED('Table_Month')
    )

VAR EndMonthYear =
    CALCULATE(
        MAX('Table_Month'[Month-Year]),
        ALLSELECTED('Table_Month')
    )

VAR StartSpend =
    CALCULATE([sumspend], 'Table_Month'[Month-Year] = StartMonthYear)

VAR EndSpend =
    CALCULATE([sumspend], 'Table_Month'[Month-Year] = EndMonthYear)

RETURN
    IF(
        ISFILTERED('Table_Month'[Month-Year]),
        DIVIDE(EndSpend - StartSpend, StartSpend, 0),
        0
    )

[grazitti_sapna]

Hi @Anonymous, 
You can try using,

Percent change of spend =
VAR StartMonthYear =
MINX ( VALUES ( 'Table_Month' ), 'Table_Month'[MonthYearNumber] )
VAR EndMonthYear =
MAXX ( VALUES ( 'Table_Month' ), 'Table_Month'[MonthYearNumber] )
VAR StartSpend =
CALCULATE ( [sumspend], FILTER ( 'Table_Month', 'Table_Month'[MonthYearNumber] = StartMonthYear ) )
VAR EndSpend =
CALCULATE ( [sumspend], FILTER ( 'Table_Month', 'Table_Month'[MonthYearNumber] = EndMonthYear ) )

RETURN
IF (
ISFILTERED ( 'Table_Month'[Month-Year] ),
DIVIDE ( EndSpend - StartSpend, StartSpend ),
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!

[DataNinja777 · Accepted solution]

Hi @Anonymous ,

The issue occurs because LASTNONBLANK and FIRSTNONBLANK determine the startdate and enddate based on the lexicographical (text-based) order of Month-Year. Since the slicer stores Month-Year as text, it treats "January 2025" as earlier than "December 2024", leading to an incorrect sign for the percent change. To fix this, we need to use the numeric sorting column (e.g., MonthYearNumber) to correctly determine the chronological order.

The updated formula ensures that the start date is always the earliest selected month and the end date is always the latest selected month by using MINX and MAXX on MonthYearNumber. This prevents the issue where months in different years get misinterpreted. Here’s the corrected DAX measure:

Percent change of spend =
VAR StartMonthYear =
    MINX ( VALUES ( 'Table_Month' ), 'Table_Month'[MonthYearNumber] )
VAR EndMonthYear =
    MAXX ( VALUES ( 'Table_Month' ), 'Table_Month'[MonthYearNumber] )
VAR StartSpend =
    CALCULATE ( [sumspend], 'Table_Month'[MonthYearNumber] = StartMonthYear )
VAR EndSpend =
    CALCULATE ( [sumspend], 'Table_Month'[MonthYearNumber] = EndMonthYear )

RETURN
    IF (
        ISFILTERED ( 'Table_Month'[Month-Year] ),
        DIVIDE ( EndSpend - StartSpend, StartSpend ),
        0
    )

This formula ensures that December 2024 is treated as earlier than January 2025 based on MonthYearNumber, preserving the correct direction of the percent change calculation.

Best regards,

[Anonymous]

Thank you so so much. I have been trying to figure this out for hours and you helped me solve it so quickly. I really appreciate your help!