Forum Discussion

Schwabinsky's avatar
Schwabinsky
New Member
1 year ago

Changing team assignment solution

Hello everyone, I have the following problem when customizing my report. We already have a report that tracks the workload of our project teams. Simplified, the report consists of a project table (p...
Need Help
danextian's avatar
danextian
Super User
1 year ago

Hi Schwabinsky 

 

I have a general idea of what to do but without a sample data it's hard to test a solution. Please provide a workable sample data (not an image), your expected result from that and the reasoning behind.

  • Schwabinsky's avatar
    Schwabinsky
    New Member
    1 year ago

    Hi denextian, thank you for your fast response. In the following, ich will show you the example data.

     

    Iputdata:

    dim_Employee:

    EmployeeIDEmployeeNameTeamStartDateEndDate
    1001Max MustermannTeam A01.01.202401.01.2100
    1001Max MustermannTeam B01.01.200031.12.2023
    1002Jane DowTeam C01.01.202401.01.2100
    1002Jane DowTeam D01.01.200031.12.2023

     

    dim_Date:

    DateDateKeyYearYearMonthQuarterDayOfMonthCalendarWeekDayOfYear
    30.12.20232023123020232023-1243052364
    31.12.20232023123120232023-1243152365
    01.01.20242024010120242024-011111
    02.01.20242024010220242024-011212
    03.01.20242024010320242024-011313
    04.01.20242024010420242024-011414
    05.01.20242024010520242024-011515

     

    fact_Projecthours:

    ProjectProjectprocessDateFK_EmployeeHours
    10189850001.01.202410014
    10189851002.01.202410014
    10189852003.01.202410024
    10189853004.01.202410024
    10190060001.01.202410016
    10190061002.01.202410016
    10190062003.01.202410026
    10190063004.01.202410026

     

    The following visual shows the data as it should look if a date between “01.01.2024” and “01.01.2100” is selected via a date slicer. If you now change the date to a date in the past in the period from “01.01.2000” to “21.12.2023”, then the team of Max Mustermann should change to “Team B” and the team of “Jane Dow” should change to “Team D”:

    Drillthrough (Team > Employee > Project)JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
    Team A16           
       Max Mustermann 16           
          10189816           
    Team C24           
       Jane Dow24           
          10190024           

     

    Thank you!

    Schwabinsky