Complex Hardcode with Dates and Teams

Good morning Team. Would appreciate some assistance with this current problem I’m trying to work through. Essentially, I’m trying to hardcode multiple values for a report only measure that pulls from two tables. Time: in Rows and Teams: in Columns. In my horrible drawing conception. I know the report for the BLUE column and I know how to make the measure for the GREEN columns.

Hardcoding the data for the YELLOW column is where I’m lost.

Is there a specific CASE example I can use that would pull the Week Rollup and then when I have the specific team selected… display a specific value that was hardcoded?

The Case would be… When X Team is selected (In columns) display the various static values in this COLUMN that corresponds to a specific weekly range in the associated ROW.

Thanks in advance! Have a great day.


Hi @rodjay5
You can create CASE WHEN construction in your measure for the combinations of your team value and week number. Here is an example where I used “Project team”:

  [Project team].CurrentMember.Name ='Ghost'AND [Time].CurrentHierarchyMember.Name = 'W01, Dec 30 2019' 
  THEN 10
    [Project team].CurrentMember.Name ='Ghost'AND [Time].CurrentHierarchyMember.Name = 'W02, Jan 06 2020' 
    THEN 15
    [Project team].CurrentMember.Name ='Astronaut' AND [Time].CurrentHierarchyMember.Name = 'W01, Dec 30 2019' 
    THEN 20
    [Project team].CurrentMember.Name ='Unicorn' AND [Time].CurrentHierarchyMember.Name = 'W01, Dec 30 2019' 
    THEN 30
    [Project team].CurrentMember.Name ='Robot' AND [Time].CurrentHierarchyMember.Name = 'W01, Dec 30 2019'
    THEN 40

In the report:

Gerda //