Forum Discussion

Atiroocky's avatar
Atiroocky
Frequent Visitor
2 years ago
Solved

Column in datatable with dynamic function paramater

Hello,   I managed to calculate a cumulative rolling day in a column of my table. I use the function "DATESINPERIOD"   I would like to replace the "static" number parameter by a dynamic paramete...
  • tamerj1's avatar
    tamerj1
    2 years ago

    Hi Atiroocky 
    Calculated columns cannot be dynamic as they do not interact with the fiter conext of the report. This has to be a measure

    Count =
CALCULATE (
    SUM ( 'Table_DHU'[CityLocation] ),
    ALL ( 'Table_DHU' ),
    DATESINPERIOD (
        'Table_DHU'[Date],
        MAX ( 'Table_DHU'[Date] ),
        - [Parameter],
        DAY
    )
)
tamerj1's avatar
tamerj1
Community Champion
2 years ago

Hi Atiroocky 
Calculated columns cannot be dynamic as they do not interact with the fiter conext of the report. This has to be a measure

Count =
CALCULATE (
    SUM ( 'Table_DHU'[CityLocation] ),
    ALL ( 'Table_DHU' ),
    DATESINPERIOD (
        'Table_DHU'[Date],
        MAX ( 'Table_DHU'[Date] ),
        - [Parameter],
        DAY
    )
)
  • Atiroocky's avatar
    Atiroocky
    Frequent Visitor
    2 years ago

    Thanks, that works perfectly !

    As a "beginner" I’m not very comfortable playing with "Measures". A bit earlier before posting in this forum, I tried to put the column code in a measure, but I used Table_DHU[Date] as start date instead of "MAX(Table_DHU[Date])". I was returning me an error.
    For me "MAX" was the last entry of all table data. But I was thinking as "table" data and no "measure" data. It is still a bit confused in my mind but time will help.

     

    Thanks again !

    • tamerj1's avatar
      tamerj1
      Community Champion
      2 years ago

      Atiroocky 
      MAX ( 'Table'[Column] ) when used in a calculated column returns the max value in this column becuase there is no filter context. However when used in a measure it will return the max value avialable in the current filter context. In other words, the value is different from one row to another.

      • Atiroocky's avatar
        Atiroocky
        Frequent Visitor
        2 years ago

        Thanks for your explanation. That helps me better understanding.