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

 

 

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

3 Replies

  • 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