CODEWITHSUNDEEP
daxssas-tabularssas-2016
Neil P
Neil P

Reputation: 3190

DAX - PARALLELPERIOD() to get the previous minute

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.

Upvotes: 0

Views: 179

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

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))

Upvotes: 0

Related Questions