Trying to calculate the previous date

[ansa_naz]

My data is in the following format:

Date                  Value

01/01/2020       100.00

01/01/2020       12.55

31/01/2020       50.00

02/02/2020       95.65

02/02/2020       1256.00

08/02/2020       62.55

I want to return the following in a matrix:

Current Date                 Previous Date

01/01/2020                    

31/01/2020                   01/01/2020

02/02/2020                   31/01/2020

08/02/2020                   02/02/2020

I have the following measure written for Previous Date but I am getting a blank:

Previous Date =
VAR a =
    MAX ( 'Lift Contract History'[Date] )
VAR b =
    CALCULATE (
        MAX ( 'Lift Contract History'[Date] ),
        FILTER ( 'Lift Contract History', 'Lift Contract History'[Date] < a )
    )
RETURN
    b

Am I doing something obviously wrong? Many thanks for all help

[V-lianl-msft]

Hi @ansa_naz ,

Create the index column in the query editor first.

Get the previous date by index:

Measure = var current_index =SELECTEDVALUE('Table'[Index])
var p_date = CALCULATE(FIRSTNONBLANK('Table'[date],1),FILTER(ALL('Table'),'Table'[Index]=current_index-1))
return p_date

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harshnathani]

Hi @ansa_naz ,

You can create a Calculated Column,

Previous Date = CALCULATE(MAX(Table6[Date]),FILTER(Table6,Table6[Date] < EARLIER(Table6[Date])))

Regards,

Harsh Nathani

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

[az38 · Accepted solution]

@ansa_naz 

does it work? it seems ok from the first sight

also you can try to use all()

Previous Date =
VAR a =
    MAX ( 'Lift Contract History'[Date] )
VAR b =
    CALCULATE (
        MAX ( 'Lift Contract History'[Date] ),
        FILTER ( ALL('Lift Contract History'), 'Lift Contract History'[Date] < a )
    )
RETURN
    b

[ansa_naz]

Thanks @az38  adding in the ALL did the trick, cant believe i forgot to do that! You da man!