Problem with Dynamic Format when Calculating Time Until Expiration

[Jack_Frost2022]

I'm trying to create a measure in Power BI that calculates the time remaining until a solution expires. To do this, I'm subtracting the current time using NOW() from the 'solve_expiration' date. After that, I convert the result to a double value so I can use Power BI's 'Dynamic' format.

EXPIRATION SOLUTION = 
var vSolve = FIRSTNONBLANK( VALUES( tickets_latest[solve_expiration] ), tickets_latest[solve_expiration] )
var vTime = IF( NOT ISBLANK( vSolve ), vSolve - NOW() )

RETURN
CONVERT( vTime , DOUBLE )

The issue is that when I apply the 'dynamic' format, it produces some incorrect results. For example, it sometimes adds more days than it should, shows more than 24 hours in a single day, and gets some hour values wrong:

var vTime = SELECTEDMEASURE()
var vSignTest = vTime < 0
var vAbsTime = ABS( vTime )

var vDay = INT( vAbsTime )
var vHour = INT( ( vAbsTime - vDay ) * 24)
var vMinute = INT( ( ( ( vAbsTime - vDay ) * 24 ) - vHour ) * 60 )

var vHours = FORMAT( vHour , "00" ) & "h"
var vMinutes = FORMAT( vMinute , "00" ) & "m"

RETURN
IF(
    NOT ISBLANK( vTime ), 
    IF( vSignTest , 
        "Overdue by " & 
        IF( vDay > 1 , vDay & " days;" & vDay & " days", vDay & " day;" & vDay & " day" )
    , 
        IF( vDay = 0 , "Due in " & vHours & vMinutes, 
            IF( vDay > 1 , vDay & " days " & vHours & vMinutes, vDay & " day " & vHours & vMinutes )
        )
    )
)

When I use a separate measure to format the result as a text string, the value appears correctly. However, when it's formatted as text, I can only sort it alphanumerically, not by the actual numeric time value.

EXPIRATION SOLUTION STR = 
var vSolve = FIRSTNONBLANK( VALUES( tickets_latest[solve_expiration] ), tickets_latest[solve_expiration] )
var vTime = IF( NOT ISBLANK( vSolve ), vSolve - NOW() )

var vDay = INT( ABS( vTime ) )
var vHour = HOUR( vTime )
var vMinute = MINUTE( vTime )

var vHours = FORMAT(vHour, "00h")
var vMinutes = FORMAT(vMinute, "00m")

RETURN
IF(
    NOT ISBLANK( vTime ), 
    IF( vTime < 0 , 
        "Overdue by " & 
        IF( vDay > 1 , vDay & " days" , vDay & " day" )
    , 
        IF( vDay = 0 , "Due in " & vHours & vMinutes , 
            IF( vDay > 1 , vDay & " days " & vHours & vMinutes , vDay & " day " & vHours & vMinutes )
        )
    )
)

I’m aware that I could handle this by creating two new columns in the source table—one formatted as text and another as a numeric value for sorting—but I want the calculation to happen in real time using the NOW() function, without needing to refresh the dataset to get the latest time values.

Also, I can’t use a format like 'd HH:mm' because some durations exceed 31 days, and this format doesn’t display the total day count correctly.

Can anyone help?

Thanks in advance!

[v-sathmakuri]

Hi @Jack_Frost2022 ,

I hope the information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @Jack_Frost2022 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[danextian]

Hi @Jack_Frost2022 

You can create a measure to calculate the time difference and use it to rank the Record IDs. If you prefer not to display the rank directly, you can append a zero-width space character—repeated according to the rank value—to the Expiration Solution measure, keeping the sort order without showing the rank visibly.

Expiration Solution Rank = 
VAR NowTime = NOW()
VAR ExpirationTable =
    ADDCOLUMNS(
        ALLSELECTED('Table'),
        "MinutesRemaining", DATEDIFF(NowTime, 'Table'[solve_expiration], MINUTE)
    )
VAR CurrentMinutes =
    DATEDIFF(NowTime, MAX('Table'[solve_expiration]), MINUTE)
RETURN
RANKX(
    ExpirationTable,
    [MinutesRemaining],
    CurrentMinutes,
    DESC,
    DENSE
)

Expiration Solution Measure = 
VAR ExpirationTime =
    SELECTEDVALUE ( 'table'[solve_expiration] )
VAR NowTime =
    NOW ()
VAR TotalMinutes =
    IF (
        NOT ( ISBLANK ( ExpirationTime ) ),
        DATEDIFF ( NowTime, ExpirationTime, MINUTE )
    )
VAR AbsMinutes =
    ABS ( TotalMinutes )
VAR Days =
    QUOTIENT ( AbsMinutes, 1440 )
VAR Hours =
    QUOTIENT ( MOD ( AbsMinutes, 1440 ), 60 )
VAR Minutes =
    MOD ( AbsMinutes, 60 )
VAR TimeText =
    FORMAT ( Days, "0" ) & " days "
        & FORMAT ( Hours, "0" ) & "h "
        & FORMAT ( Minutes, "0" ) & "m"
VAR _result =
    IF (
        ISBLANK ( ExpirationTime ),
        BLANK (),
        IF ( TotalMinutes >= 0, TimeText, "Expired " & TimeText & " ago" )
    )
RETURN
    REPT ( UNICHAR ( 8203 ), [Expiration Solution Rank] ) & _result

Please see the attached pbix.

[Jack_Frost2022]

Hi, @danextian, thank you for the detailed explanation and the suggestion using the zero-width space character for ordering.

I had actually considered implementing something similar using the method shown in this video:
Sort a measure formatted as text in Power BI 

However, my main goal was to sort the visual directly based on the numeric value of the time remaining, while also displaying a nicely formatted duration (e.g., 2 days 03h 15m) directly in the measure.

The real issue I'm running into is that when I apply Power BI's dynamic format to a numeric measure representing a datetime difference, the output seems inconsistent:

• Sometimes it shows more than 24 hours per day.

• Sometimes it adds more days than expected.

• The hour/minute breakdown isn’t always accurate, especially around edge cases (e.g., around midnight).

That’s why I switched to formatting the string manually (as shown in my 'EXPIRATION SOLUTION STR' measure), which works perfectly visually, but then I lose the ability to sort numerically.

I’d love to understand why the dynamic formatting behaves this way. My guess is that converting a time difference (DateTime - NOW()) into DOUBLE is subject to how Power BI interprets fractional days, but I couldn’t find a clear explanation or consistent behavior.

So I’m stuck between:

• Accurate formatting but no numeric sorting, and

• Numeric value that sorts correctly but is formatted incorrectly.

If you happen to know what’s going on under the hood with the dynamic formatting, or if there’s a way to "nudge" Power BI into treating the difference more consistently, I’d love to learn more about it.

Thanks again for your help — and for all the content you share! Really appreciate it.

[v-sathmakuri · Accepted solution]

Hi @Jack_Frost2022 ,

Thank you for reaching out to Microsoft Fabric Community.

Sorting the data using the numeric measure will correctly order the results. i have attached the sample PBIX file that includes the 'Expiration Solution' measure. Please let us know if you encounter any further issues.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[lbendlin]

After that, I convert the result to a double value so I can use Power BI's 'Dynamic' format.

No need to do that.  You can do you math directly on the Datetime values.

You need to treat the days separately from the hours and minutes though, as you do. There is a mean, mean stumbling block in that INT  is not doing what it is supposed to do. Instead you need to use ROUNDDOWN

var vTime = COALESCE(vSolve,NOW()) - NOW() 
return ROUNDDOWN(vTime,0) & FORMAT(vTime," \d\a\y\s h \h\o\u\r\s m \m\i\n\u\t\e\s")