cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

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       Task Name

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
Community Support

Hi, @ET_Phone2 

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.

View solution in original post

4 REPLIES 4
Community Support
Community Support

Hi, @ET_Phone2 

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.

View solution in original post

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 IV
Super User IV

@ET_Phone2 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/3395....
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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




 

EDIT: 

 

@Greg 

 

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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors