Forum Discussion

freginier's avatar
freginier
Solution Sage
3 years ago
Solved

Dynamic calculated column

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

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

     

    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

freginier's avatar
freginier
Solution Sage
3 years ago

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's avatar
Greg_Deckler
Community Champion
3 years ago

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

 

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