DAX - PARALLELPERIOD() to get the previous minute

Question

In DAX, I want to get the value for a previous minute.

I know the PARALLELPERIOD function can do this for the date and year, but does not allow this to be done by minute.

Is there an easy way I can write the below:

[DateTime] = [DateTime] - ( 1 / 24 / 60 )

I'm new to DAX and therefore aren't sure how to alias the two "DateTime"s in my filter condition (one for the universe of the calculate function, the other referring to the row in my original query)

I know of the EARLIER () function, but I don't understand how to define the order, to be my time column.

Edit:

My measure looks like the below:

Total:= SUM('Table'[CountColumn])

I've started to write a measure to try and get the previous minute:

PreviousMinuteTotal:= CALCULATE (
    SUM ( 'Table'[CountColumn] ),
    'Table'[DateTime]
            = 'Table'[DateTime]  - ( 1 / 24 / 60 ) )

Obviously this is a contradiction as a date cannot be both itself and equal to the previous value, so I'm trying to work out how add a filter in calculate that looks back by one minute.

`EARLIER()` is not really a time-related function. It's for maneuvering nested filter contexts. — Alexis Olson, Dec 05 '17 at 16:56
Can you provide a bit more context on how you are looking to use this? Are you trying to write a measure or a calculated column? What does your formula look like supposing what you wrote worked like you wanted? — Alexis Olson, Dec 05 '17 at 17:02
@AlexisOlson I'm wanting to build a measure that gets the total from my table, for the previous minute. Therefore I will end up with two measures, Total and previousMinuteTotal — Neil P, Dec 05 '17 at 17:04
If you show me what your total measure looks like, I think I can help you write the previous minute total measure. — Alexis Olson, Dec 05 '17 at 17:13
OK. Now if your time is 1:31:56, are you looking for the sum of the previous minute (1:30:00 - 1:30:59) or the previous 60 seconds (1:30:56 - 1:31:55)? — Alexis Olson, Dec 05 '17 at 17:30
@AlexisOlson So sorry, I should have explained that all my datetime values are truncated to the nearest minute. So it will only ever have 1:31:00 and 1:30:00 — Neil P, Dec 05 '17 at 17:45

Alexis Olson · Answer 1 · 2017-12-05T18:13:17.857

Try something like this:

PreviousMinuteTotal = 
    VAR PreviousMinute = MAX('Table'[DateTime]) - TIME(0,1,0)
    RETURN CALCULATE(SUM('Table'[CountColumn]), 'Table'[DateTime] = PreviousMinute)

I had a bit of trouble (due to rounding maybe?) using this, so you might need to try this for the return line instead. (I added a second of wiggle room.)

RETURN CALCULATE(SUM('Table'[CountColumn]),
           'Table'[DateTime] > PreviousMinute - TIME(0,0,1),
           'Table'[DateTime] < PreviousMinute + TIME(0,0,1))

DAX - PARALLELPERIOD() to get the previous minute

1 Answers1