Previous Value on row is wrong and is can't get per timestamp, only per day

[Anonymous]

Hi, I have a calculated column that should get the previous timestamp budget amount, however this is only showing the previous date, not timestamp, my usuage is then wrong, which makes my report show a total which is completely wrong, can anyone help with this?

Previous timestamp value is wrong on two dates, first 12/13/2018 and secondly on 1/2/2020

This is the column that is wrong and not always showing the previous value:

Usage since last ts is simply:

Usage Since Last TS = IF(NOT(ISBLANK('Table'[Previous TimeStamp Value])),

        'Table'[totalBudgetAmount] - 'Table'[Previous TimeStamp Value])

Thanks!

[harshnathani]

Hi @Anonymous ,

1. Check the Data Type in Power Query . It should be Date Time.

2. Check whether any values are getting summarised by default in the pane. Make all as Don't Summarize.

You can use these measures too

Use these Measures:

Previous CaseId =
VAR a =
    MAX ( 'Table'[originalTimeStamp] )
VAR b =
    CALCULATE (
        MAX ( Table[caseId] ),
        FILTER (
            ALL (
                Table[CaseID],
                Table[originalTimeStamp],
                Table[totalBudgetAmount]
            ),
            Table[originalTimeStamp] < a
                && Table[CaseID]
                    = MAX (  Table[CaseID] )
        )
    )
RETURN
    b



Previous DateStamp =
VAR a =
 MAX ( 'Table'[originalTimeStamp] )
VAR b =
CALCULATE (
MAX ( Table[originalTimeStamp] ),
 FILTER (
            ALL (
                Table[CaseID],
                Table[originalTimeStamp],
                Table[totalBudgetAmount]
            ),
            Table[originalTimeStamp] < a
                && Table[CaseID]
                    = MAX (  Table[CaseID] )
)
)
RETURN
b

Previous Value2 =
VAR _previousDate = [Previous DateStamp]
VAR _previousCase = [Previous CaseId]
RETURN
    CALCULATE (
        MAX ( Table[totalBudgetAmount] ),
        FILTER (
            ALL ( Table),
           Table[originalTimeStamp] = _previousDate
                && Table[CaseID] = _previousCase
        )
    )

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[amitchandak · Accepted solution]

@Anonymous , try both a new columns

Previous TimeStamp = maxx(filter(Table, [case id] =earlier([case id]) && [original Time Stamp] <earlier([original Time Stamp])),[original Time Stamp])

Previous TimeStamp Value= maxx(filter(Table, [case id] =earlier([case id]) && [original Time Stamp] =earlier([Previous TimeStamp])),[totalBudgetAmount])