Re:

Dear Power Bi community, I would like to create a measure or column (not very sure since I'm new to Power BI) that gives out the Value of my timestamp every 20 Minutes to analyse the tank fill lev...

OwenAuger

4 years ago

HI frittle

Interesting question 🙂

I have attached a sample PBIX.

This is how I would recommend setting this up:

Set up the fact table (I've called it Measurement below), with separate Date and Time columns, with Time at the 20 minute granularity (see here for discussion):
1. Create Timestamp Bin (as you already have), but just containing the time part.
2. Create a Date column containing just the date part (there wasn't a date in your example table, but I assume there must be one).
3. The original Timestamp column can be optionally removed (as it is a high cardinality column), and I recommend creating with a Timestamp Index column solely for the purpose of breaking ties between two Timestamps in the same bin.
4. This is how the final Measurement table looks in my PBIX (my date format is d/mm/yyyy):
Create Date and Time dimension tables, with Time being at 20 minute granularity.
Mark Date table as a date table.
Create relationships so the data model looks like this, with :
Create measures as follows, with Last Value being the final measure to use in visuals:

-- ===================================================
-- Value Average
-- Base measure averaging the Value column
-- ===================================================
Value Average = 
AVERAGE ( Measurement[Value] )

-- ===================================================
-- Value Average Breaking Ties
-- If multiple Timestamp indexes exist in the same bin
-- use the one with the max index
-- ===================================================
Value Average Breaking Ties = 
AVERAGEX (
    SUMMARIZE ( Measurement, 'Date'[Date], 'Time'[Time] ),
    -- Use Timestamp Index to break ties.
    LASTNONBLANKVALUE ( Measurement[Timestamp Index], [Value Average] )
)

-- ===================================================
-- Last Value
-- Takes all Date/Time values existing in Measurement
-- up to the max filtered date, determines the max
-- Date/Time, and returns [Value Average Breaking Ties]
-- for that Date/Time.
-- ===================================================
Last Value = 
VAR OverallMaxDateTime =
    CALCULATE (
        MAXX ( Measurement, 'Measurement'[Date] + Measurement[Timestamp bin] ),
        REMOVEFILTERS ()
    )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MaxTime =
    MAX ( 'Time'[Time] )
VAR MaxDateTime =
    MaxDate + MaxTime
RETURN
    IF (
        MaxDateTime <= OverallMaxDateTime,
        VAR PastDateTime =
            FILTER (
                CALCULATETABLE (
                    SUMMARIZE (
                        Measurement,
                        'Date'[Date],
                        'Time'[Time]
                    ),
                    'Date'[Date] <= MaxDate,
                    REMOVEFILTERS ( 'Time' )
                ),
                'Date'[Date] + 'Time'[Time] <= MaxDateTime
            )
        VAR LatestDateTimeWithValue =
            TOPN (
                1,
                PastDateTime,
                'Date'[Date] + 'Time'[Time]
            )
        VAR Result =
            CALCULATE (
                [Value Average Breaking Ties],
                LatestDateTimeWithValue,
                REMOVEFILTERS ( 'Time' ) -- Time filters must be explicitly removed
            )
        RETURN
            Result
    )

Notes on Last Value:

The Last Value is the measure to display on visuals.
I tried a few approaches, including some built-in functions LASTNONBLANK/LASTNONBLANKVALUE, but the above code performed best in my testing.
It will only return values up to the latest date/time in the dataset due to this condition (this can be changed):
MaxDateTime <= OverallMaxDateTime
With the logic in the above measures, the Last Value at 10:00 is 76, since that had the later timestamp in the 10:00 bin.

Sample table in report:

I realise that was quite a long-winded answer, but hopefully the sample PBIX makes it clearer.

Please post back with any other question 🙂

Regards,

Owen

Select last value 20 minute interval.pbix32 KB