Show previous date in data.

[Anonymous]

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

[v-easonf-msft · Accepted solution]

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]

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

[Greg_Deckler]

@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

[Anonymous]

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