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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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

  • 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