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

Anonymous
Not applicable

## Show previous date in data.

Hi all,

I'm hoping you could help me. I have tried every DAX formula under the sun that I can think of to get this right but I can't see to get anyway. I either get error messages or blank columns.

So currently I have data that is produced each quarter (however it could be monthly or two monthly going forward so it really depends when the data is run). Each file of data will list names and when each person completed certain tasks for example:

Name.1    28/7/2019

Name.2    07/10/2020

etc. etc.

The data next to each name will only update when that person has completed the task (so it could be several quarters, years before the date is updated for instance).

Currently my visual shows the most up to date using the MAX function however I'm hoping to also show the previous date as well and from there if there is no previous date then use the MAX date.

I have tried the following DAX formulas to no avail:

Previous = CALCULATE(MAX('Table1'[Date]), Filter('Table2', 'Table2'[Input Order] <> MAX ('Table1'[Month]))))

Previous = Var MaxDate = Calculate(MAX('Table1'[Date]), ALL('Table1'[Date])) Return CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < MaxDate (I took this formula from another forum)

Previous = CALCULATE(SUM('Table1'[Date] < 'Table1'[MAX Data Measure]))

Previous = Calculate(MAX('Table1'[Date]), FILTER(ALL('Table1'[Date]), 'Table1'[Date] < SelectedValue('Table1'[Date])))

I've probably tried many more than the above.

The issue I have is that I don't have a sort of sales column to sum by, just the names and dates so I know that the SUM function wouldn't actually work but I thought I would try!

My date column is formatted to Date/Time (dd/mm/yyyy).

Thank you for any help!

E

1 ACCEPTED SOLUTION
Community Support

Hi, @Anonymous

Try to change you measure as below:

``````Previous1 =
VAR MaxDate =
CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( 'Table1'[Date] ) )
VAR PreviousDate =
CALCULATE ( MAX ( 'Table1'[Date] ), 'Table1'[Date] < MaxDate )
RETURN
IF ( ISBLANK ( PreviousDate ), MaxDate, PreviousDate )
``````

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support

Hi, @Anonymous

Try to change you measure as below:

``````Previous1 =
VAR MaxDate =
CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( 'Table1'[Date] ) )
VAR PreviousDate =
CALCULATE ( MAX ( 'Table1'[Date] ), 'Table1'[Date] < MaxDate )
RETURN
IF ( ISBLANK ( PreviousDate ), MaxDate, PreviousDate )
``````

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi,

Thank you so much, you've literally saved my bacon here. My next step is conditional formatting.

Edited out rest of message as I answered my own question and it was not connected to the main question.

Thank you,

E

Super User

@Anonymous So typically you would do this in a column using EARLIER although you can also do it using a measure if you use ADDCOLUMNS. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

EDIT:

Hi, thank you for getting back to me so quickly. I've given your formula a go. Although I would prefer this to be a measure I'm not entirely against doing this as a calculated column.

I'm having issues with the formula, it throws back an error

So for the [Value] I've been using a measure I created earlier which is Base = MAX('Table1'[Date]

The issue I'm getting is: "A circular dependency was detected: 'Table1'[Column]

I probably should have mentioned that I have all dates for all takes in just the one column.

Thanks,

E

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.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors