Forum Discussion

Jeanxyz's avatar
Jeanxyz
Power Participant
4 years ago
Solved

create a dynamic column header based on measures

I have created a timesheet matrix table as below. the current column field is Intl week number (e.g., 13, 14,15...). I need to populate the start date of each week into the column header, something like "week14 - 4/4/2022". Is there a way to populate this value to the column header. 

It's easy to populate this data in a table, but I have to use a matrix table here, hence there is an issue. 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Jeanxyz ,

     

    Please create a calculated column in the calendar table.

     

    Week_Date = 
VAR _weeknum =
    WEEKNUM ( 'Calendar'[Date], 2 )
VAR _firstdate =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER ( ALL ( 'Calendar'[Date] ), WEEKNUM ( 'Calendar'[Date], 2 ) = _weeknum )
    )
VAR _result = "Week" & _weeknum & "-" & _firstdate
RETURN
    _result

     

    Then use this column as a field of the matrix column.

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

4 Replies

  • mh2587's avatar
    mh2587
    Super User
    4 years ago

    Week Start Date/Wk No = CONCATENATE(Data[Date] – WEEKDAY(Data[Date],2) + 1,Intl week number)
    //try this one

    • Jeanxyz's avatar
      Jeanxyz
      Power Participant
      4 years ago

      The thing is I can't drag a measure into the column field. I have create a tooltip page to show weekstart data. Maybe someone has a better solution. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Jeanxyz ,

         

        Please create a calculated column in the calendar table.

         

        Week_Date = 
VAR _weeknum =
    WEEKNUM ( 'Calendar'[Date], 2 )
VAR _firstdate =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER ( ALL ( 'Calendar'[Date] ), WEEKNUM ( 'Calendar'[Date], 2 ) = _weeknum )
    )
VAR _result = "Week" & _weeknum & "-" & _firstdate
RETURN
    _result

         

        Then use this column as a field of the matrix column.

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly -- How to provide sample data

  • Jeanxyz's avatar
    Jeanxyz
    Power Participant
    4 years ago

    Thank you! Your solution worked. I had forgotten to accepted it as a solution though.