cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Continued Contributor

## Trying to calculate the previous date

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

1 ACCEPTED SOLUTION
Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
4 REPLIES 4
Community Support

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.

Community Champion

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!!
Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
Continued Contributor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.