Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ElliotP
Post Prodigy
Post Prodigy

Showing only the last 20,40,60 minutes

Evening,

 

I've got some transaction data that I'm receiving live and I'd like to only showing the past 20minutes.

 

I've tried filtering the data table dynamically but the date and time column are seperate and don't seem to like being merged (errors out). The time column doesn't have the feature for "in the previous 20mins".

 

So I felt like we could create a dax measure to achieve this; I have a similar measure using variables for the week of the year.

 

P0WNetSales = 
VAR CurrentYear =
MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
CALCULATE (
MAX ( 'ExtendedCalendar'[WeekNum] ) - 0, FILTER(ALL( 'ExtendedCalendar' ), 'ExtendedCalendar'[Year] = CurrentYear )
)
RETURN

ABC


'ExtendedCalendar'[Year] = CurrentYear,
'ExtendedCalendar'[WeekNum]
= CurrentWeekNum
)

I created a 20minute time slot for the hour of the day:

 

TimeSlot = 
SWITCH(
 TRUE(),
 HourMinuteSecond[Minute] < 20,HourMinuteSecond[Hour12]& ".00"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".19"&HourMinuteSecond[AMPM], 
 HourMinuteSecond[Minute] < 40,HourMinuteSecond[Hour12]& ".20"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".39"&HourMinuteSecond[AMPM], 
 HourMinuteSecond[Hour12]& ".40"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".59"&HourMinuteSecond[AMPM]
 )

And this to sort it by (TimeSlot won't sort by TimeSlotSortbyOrder due to there being multiple values at the same time, not an issue for us here though):

 

TimeSlotSortOrder = HourMinuteSecond[Hour24] * 100 +
SWITCH(
    TRUE(),
    HourMinuteSecond[Minute] < 20,1,
    HourMinuteSecond[Minute] < 40,2, 
    3    
    )

This produces a unique value for each 20minute window of the day. So 4.00am-4.19am has a timeslotorder value of 401; 4.20am-4.49am has a timeslotorder value of 402.

 

When i tried to modifier the formula to work with it; I couldn't seem to get it together.

 

I saw this link; it looks incredibly promising (http://community.powerbi.com/t5/Desktop/Only-show-last-24-hours-from-6am/m-p/109226#M45933); I just can't work out how to modifier for the past 20minutes as opposed to days.

 

Have to try to modify this:

minTime = 
var temp= NOW()
return
if(HOUR(temp)>=6,temp,DATE(YEAR(temp),MONTH(temp),DAY(temp)-1)+TIME(6,MINUTE(temp),SECOND(temp)))

So, if anyone has any ideas that would be amazing. I feel the dax code immediatly above is probably the easiest way to do it, but I'm not sure.

1 ACCEPTED SOLUTION

Hi @ElliotP,

 

My mistake.Smiley LOL

 

Try using the formula below to create a measure to see if it works this time.

Measure =
CALCULATE (
    SUM ( [NetSales] ),
    FILTER (
        'YourTableName',
        'YourTableName'[Date] + 'YourTableName'[Time]
            >= minDateTime
    )
)

 

Regards

View solution in original post

7 REPLIES 7
v-ljerr-msft
Employee
Employee

Hi @ElliotP,


Have to try to modify this:

minTime = 
var temp= NOW()
return
if(HOUR(temp)>=6,temp,DATE(YEAR(temp),MONTH(temp),DAY(temp)-1)+TIME(6,MINUTE(temp),SECOND(temp)))

So, if anyone has any ideas that would be amazing. I feel the dax code immediatly above is probably the easiest way to do it, but I'm not sure.


Could you try the formula below to see if it works in your scenario?Smiley Happy

minDateTime =
VAR currentTime =
    NOW ()
VAR previousDayTime = currentTime - 1
RETURN
    IF (
        HOUR ( currentTime ) = 0
            && MINUTE ( currentTime ) < 20,
        DATE ( YEAR ( previousDayTime ), MONTH ( previousDayTime ), DAY ( previousDayTime ) )
            + TIME ( 23, MINUTE ( previousDayTime ) + 60 - 20, SECOND ( previousDayTime ) ),
        IF (
            MINUTE ( currentTime ) >= 20,
            DATE ( YEAR ( currentTime ), MONTH ( currentTime ), DAY ( currentTime ) )
                + TIME ( HOUR ( currentTime ), MINUTE ( currentTime ) - 20, SECOND ( currentTime ) ),
            DATE ( YEAR ( currentTime ), MONTH ( currentTime ), DAY ( currentTime ) )
                + TIME ( HOUR ( currentTime ) - 1, MINUTE ( currentTime ) + 60
                    - 20, SECOND ( currentTime ) )
        )
    )

 

Regards

Amazing, that looks like the right idea.

 

How would I add in a CALCULATE(SUM(NetSales))?

Hi @ElliotP,

 

The formula should be similar like below.

=
CALCULATE (
    SUM ( [NetSales] ),
    'YourTableName'[Date] + 'YourTableName'[Time]
        >= minDateTime
)

If that is not the case, please post your table structures and relationships between them.Smiley Happy

 

Regards

As a measure or as a calculated column?

 

 When i attempt to add it as a measure it doesn't recognise the + time table and the >= minDateTime aspect of the filter, thoughts?

Hi @ElliotP,

 

My mistake.Smiley LOL

 

Try using the formula below to create a measure to see if it works this time.

Measure =
CALCULATE (
    SUM ( [NetSales] ),
    FILTER (
        'YourTableName',
        'YourTableName'[Date] + 'YourTableName'[Time]
            >= minDateTime
    )
)

 

Regards

Afternoon. That's work perfectly, thank you so much. It's incredibly cool to see it working, thank you so much.

Working on an idea; any idea how to show for example;

 

Last 20minutes (can do that already) as well as then 20 minutes before that? I've tried editing the mindatetime measure but I can't seem to get it to come together

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.