Dynamic calculated column

[freginier]

Dear all, 

I use this calculated column to get the value from 5 days ago for each row. The formula works very well but now I would be able to replace the number of day by variable. 

I try to use parameter table but I can't capture slicer selected value in a column.. How can I do ? 

Previous Value (5) = 
VAR __PreDate = Transaction[Date] - 5
VAR __PreValue = MAXX(FILTER(Transaction,Transaction[Date] = __PreDate && Transaction[Group] = EARLIER(Transaction[Group])),Transaction[Value])
return __PreValue

Thank you 

[Greg_Deckler]

@freginier Calculated columns are not dynamic, they are only calculated at the time of refresh. You likely need to use a measure instead.

[freginier]

@Greg_Deckler  I know that I need to use a Measure but how to convert my column to Measure ?

How to use earlier in a measure ? 😊

[Greg_Deckler · Accepted solution]

@freginier Oh, you can use EARLIER in a measure by using ADDCOLUMNS. But in your case I would just do this:

Previous Value (5) = 
VAR __TransactionGroup = MAX('Transaction'[Group])
VAR __PreDate = MAX(Transaction[Date]) - 5
VAR __PreValue = MAXX(FILTER(Transaction,Transaction[Date] = __PreDate && Transaction[Group] = __TransactionGroup ),Transaction[Value])
return __PreValue

More or less this pattern:

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

Also, the Quick Measure Gallery entry demonstrates using EARLIER in a measure: (2) Mean Time Between Failure (MTBF) - Microsoft Power BI Community
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