Re:

[frittle]

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 level over a day. I get my data using an SQL script, however I only receive a value once there's a change in the fill level of tank 1. Right now what I have in Power Bi is the table you see on the left side (had to recreate it in excel before uploading it on here due to privacy).  What I would like is a time stamp Column or table like you see in the examplatory table at the bottom, that has fixed 20 minute intervals over 24 hours and selects the value of the last timestamp if no value is found for the selected timestamp (since that means that the value has not changed). To sum my problem up: I want a column of 20 minute intervalls that always gives out a value for each timestamp. If no value is found for the selected timestamp on the slicer it should give out the value of most recent timestamp. I would greatly appreciate input on this problem if anyone has a soloution. Many thanks in advance!

Best Regards

[OwenAuger · Accepted solution]

HI @frittle 

Interesting question 🙂

I have attached a sample PBIX.

This is how I would recommend setting this up:

1. 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):
      
2. Create Date and Time dimension tables, with Time being at 20 minute granularity.
3. Mark Date table as a date table.
4. Create relationships so the data model looks like this, with :
   
5. 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

[frittle]

@OwenAuger 

Hello Owen,
following up on this topic there is a new function that I have to create and I have faced a small problem, and I was wondering whether you might have an idea on how to solve it. Based on your measure that gives me the buffer fill level every 20 minutes per day (and fills in the last non blank value if no value is found), I assigned percentage Intervals (in 20% steps) based off of the maximum capacity of each buffer to every value I get using your measure with a switch function. I now count the amount every percentage interval occurs per day, so that I know if my buffer is rather empty or rather full. I display this information using a pie chart. To see what I mean check out my dashboard below:

i have two buffers "HB-G2x Speicher Bahn Zu-Ablauf" and "VB-G2x Speicher Zu-Ablauf" that I can select using the BESCHREIBUNG slicer. These two buffers have different capacities though, so I created the Table "MAXTABLE" that contains the buffers name and its maximum. 

So that the percentage calculation is right it has to use the right maximum value for the right buffer:

Puffer Max Dummy =
CALCULATE (
    SELECTEDVALUE ( MAXTABLE[MAXWERT] ),
    CROSSFILTER ( MAXTABLE[BESCHREIBUNG], 'Puffer Vergleich'[BESCHREIBUNG], BOTH )
)

Puffer Füllstand test = 
VAR CurrentValue = [Last Value final 20 minute stamp]
VAR MaxPufferInRage = [Puffer Max Dummy]
RETURN
    SWITCH (
        TRUE (),
        CurrentValue <= MaxPufferInRage * 0.2, "<20%",
        CurrentValue > MaxPufferInRage * 0.2
            && CurrentValue < MaxPufferInRage * 0.4, "20-40%",
        CurrentValue >= MaxPufferInRage * 0.4
            && CurrentValue <= MaxPufferInRage * 0.6, "40-60%",
        CurrentValue > MaxPufferInRage * 0.6
            && CurrentValue < MaxPufferInRage * 0.8, "60-80%",
        CurrentValue >= MaxPufferInRage * 0.8, ">80%"
    )

The Problem I'm facing now is that my measure "Puffer Max Dummy" gives me blanks (as you can see in the table above) If the measure "Last Value final 20 minute stamp" returns a lastnonblank value. If you have an idea on how I can fill the missing maximum values, I would highly appreciate it, since my interval count is otherwise misleading. Many thanks in advance.

Best Regards,
Leo

[frittle]

Re: 
Never mind the issue has been resolved!

[OwenAuger]

Just came back to look at this - Glad to hear it 🙂

[frittle]

No Ideas at all on how to create such a list?

[OwenAuger · Accepted solution]

HI @frittle 

Interesting question 🙂

I have attached a sample PBIX.

This is how I would recommend setting this up:

1. 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):
      
2. Create Date and Time dimension tables, with Time being at 20 minute granularity.
3. Mark Date table as a date table.
4. Create relationships so the data model looks like this, with :
   
5. 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

[frittle]

Hello Owen, many thanks for your detailed answer, this is exactly the answer I was looking for! It works with my data set, however some of the values are not 100% accurate (see pictures attached). 

The left  image is data I'm pulling via SQL and the right image is my output in power bi using your measures. Why is my value at timestamp 19:10:00 showing as 59 eventhough it should be 60? Same goes for 19:20 where my output should ideally be 55, not 53. Do you have any idea how I can make this more accurate? I don't want an average value, more like the value that comes closest to one of my timestamps. Is it not working properly becuase I'm using 5 minute intervals now somehow? Many thanks for your help so far, it is highly appreciated!!

Best Regards 

Leo

[OwenAuger]

Hi again Leo,

Glad that we have gone some way towards solving this 🙂

Thanks for checking the results. It looks like the issue is in how timestamps are rounded.

In order to show the latest Value "before or at" the binned timestamp, we need to round the original timestamps up, rather than to the closest bin.

For example:

• 19:09:30 => 19:10:00
• 19:10:30 => 19:15:00

I have adjusted my sample PBIX (attached) to round timestamps up (in Power Query, see the Measurement table step Added Timestamp bin with date (rounding up)).

Also, while there should have only been the possibility of averaging Values if there were two exactly equal timestamps, I have adjusted the sub-measures to only return single values, so there is absolutely no danger of averaging values at any stage:

Single Value = 
SELECTEDVALUE ( Measurement[Value] )

Single Value Breaking Ties = 
IF (
    -- If a single Date/Time is currently filtered
    COUNTROWS (
        SUMMARIZE ( Measurement,'Date'[Date],'Time'[Time] )
    ) = 1,
    -- Use Timestamp Index to break ties.
    LASTNONBLANKVALUE ( Measurement[Timestamp Index], [Single Value] )
)

I believe the logic is now what you expect.

Using the data from the image in your post, I get these values:

19:10 & 19:20 are as expected.

Some surrounding values may differ from your version due to date from timestamps not in the screenshot.

Let me know if this works at your end 🙂

Regards,

Owen

[frittle]

Hi Owen,

Thank you very much, this is working for me and the soloution I was looking for! I know why my values are still showing incorrectly though. It's because of my binned timestamps. For example if there is a value at timestamp 19:37:20 it will show as (probably) the highest index value for the 13:35:00 bin and give out that value. Could you show me how you set up your timestamp bin? After that every thing should work perfectly. Many thanks anyways, this is really bringing me forward!

Best Regards
Leo

[OwenAuger]

You're welcome 🙂

I used Power Query, but I'm sure it could be done in SQL too.

In the file I attached on my previous post, have a look at the Measurement query, step "Added Timestamp bin with date (rounding up)"

= Table.AddColumn(#"Changed Type", "Timestamp bin with date", 
each let TimeNumber = Number.From([Timestamp]),
TimeNumberRounded = Number.RoundUp(TimeNumber * TimesPerHour * 24) / (TimesPerHour*24)
in DateTime.From(TimeNumberRounded), type time)

The steps are:

1. Convert Timestamp (type DateTime) to a number (which will be a serial number where an interval of 1 = 1 day)
2. Multiply this number by the number of Timestamp bins per day = 24 * 5 = 120
3. Round up to the nearest integer using Number.RoundUp
4. Divide by the number of Timestamp bins per day = 120
5. Convert back to a DateTime

The parameter "Time Granularity Minutes" controls the number of of minutes between bins (should divide 60 an integer number of times to work correctly).

Regards,

Owen

[frittle]

Hi Owen,

Thanks for your quick reply and awesome help on this problem! Everything is working as it should now!

Best regards
Leo

[OwenAuger · Accepted solution]

HI @frittle 

Interesting question 🙂

I have attached a sample PBIX.

This is how I would recommend setting this up:

1. 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):
      
2. Create Date and Time dimension tables, with Time being at 20 minute granularity.
3. Mark Date table as a date table.
4. Create relationships so the data model looks like this, with :
   
5. 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