comparing 2 column and getting result till current interval

Goal: Show NCO only for intervals where Time < UpdatedOn, adjusting for each market’s time zone.

Steps:
- Create a table mapping each market to its UTC offset.
- Convert UpdatedOn (Phoenix time) to each market’s local time using offset logic.
- Compare Time < AdjustedUpdatedOn in a DAX measure.
- Use that filter to calculate NCO interval-wise.

Hi Anonymous ,

The most efficient way to achieve your goal is with a two-step process. First, you'll create a calculated helper column to handle the time zone comparison for each row. Then, you'll write a simple measure that uses this column to calculate the running total. This approach is faster because the complex time zone logic is processed only once when the data is refreshed, rather than every time you interact with your report.

You can begin by adding a new calculated column to your main data table. This column's purpose is to check if the market Time is earlier than the UpdatedOn time after converting both to a standard time zone. Be sure to replace the placeholder names with your actual table and column names.

IsTimeBeforeUpdate =
-- Define variables for clarity
VAR MarketUTCOffset = RELATED('Markets'[UTCOffset])  -- Fetches UTC offset from a related 'Markets' table
VAR PhoenixUTCOffset = -7                           -- Phoenix is always UTC-7

-- Convert both times to UTC for accurate comparison
VAR MarketTimeInUTC = 'YourTable'[Time] - (MarketUTCOffset / 24.0)
VAR UpdatedOnInUTC = 'YourTable'[UpdatedOn] - (PhoenixUTCOffset / 24.0)

-- Return 1 (True) or 0 (False) based on the comparison
RETURN
    IF(MarketTimeInUTC < UpdatedOnInUTC, 1, 0)

This DAX formula first fetches the UTC offset for the specific market from a related Markets table and sets the constant UTC-7 offset for Phoenix. It then converts both the market Time and the UpdatedOn timestamp to Coordinated Universal Time (UTC). By doing this, it creates a common baseline for an accurate comparison, returning a 1 if the condition is met and a 0 otherwise.

With the helper column in place, you can now create the final measure. This measure will calculate the running total of your NCO value up to the current time interval, but only for the rows that meet your specific time condition.

NCO Till Current Interval =
CALCULATE(
    SUM('YourTable'[NCO]), -- Your base calculation for the NCO value

    -- Filter to include all dates/times up to the current interval
    FILTER(
        ALLSELECTED('TimeDimension'),
        'TimeDimension'[DateTime] <= MAX('TimeDimension'[DateTime])
    ),

    -- Use the helper column to filter for the correct rows
    'YourTable'[IsTimeBeforeUpdate] = 1
)

For this solution to function correctly, your data model should include your main data table, a related Markets table containing the UTC offset for each market, and a TimeDimension table for time-based calculations. The measure works by using the CALCULATE function to modify the context of your NCO summation. The FILTER function generates a running total by including all time intervals up to the current point shown in your visual. Finally, it applies a simple filter on the helper column, 'YourTable'[IsTimeBeforeUpdate] = 1, ensuring the calculation only includes data where the market time was indeed before the update time.

Best regards,