Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 colum...
Need Help
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

 

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