Creating Team reports that are date sensitive

[RichardLBHF1]

I want ot be able to run reports for teams that only pick up stats for when the people are in that team. I have a table for each team. In the table is Team Number, Name, start date and end date. The data that I have in other tables includes names and dates. So in a nutshell if team member 1 was in the team from 1st Sept to Oct 10th and the slicer and the report is set for 1st August to 30th October I only want the data in the team report to count Team member 1's data from the 1st Sept. Any help would  be appreciated

[v-linhuizh-msft]

Thanks for the reply from lbendlin and DataNinja777. 

Hi @RichardLBHF1 ,

Did DataNinja777's reply solve your problem, if so, please mark it as solution. It will be helpful for other members of the community who have similar problems as yours to solve it faster. Thank you very much for your kind cooperation!

Best Regards,
Zhu

[DataNinja777 · Accepted solution]

Hi @RichardLBHF1 ,

I generated dummy data to simulate your case. To achieve your requirement, you'll be using a technique similar to the headcount analysis discussed in the link below. 

https://community.fabric.microsoft.com/t5/Community-Blog/Dynamic-Headcount-Analysis-using-Dax/ba-p/4...

In a nutshell, you only need two tables to achieve your desired output:

1. A team table with Team Number, Name, start date, and end date.
2. A disconnected calendar table.

Your data model would look like the diagram below, where the two tables remain disconnected.

Next, you would write a measure like the one below:

Team duration = 
SUMX (
    Teams,
    IF (
        Teams[Start Date] <= max('Calendar'[Date])
            && Teams[End Date] >= max('Calendar'[Date]),
        1,
        BLANK ()
    )
)

In order to create a Gantt-chart-esque visual using the standard matrix visual, I added background color to the measure by writing a measure like the one below:

ColorMeasure = 
IF(
    ISBLANK([Team duration]),
    BLANK(),
    IF([Team duration] > 0, "#FFC0CB", BLANK())  // Pink for positive numbers, no color for blank cells
)

The resulting output looks like the one below, where you can filter by fields from both the calendar table and the team table to see the status of the team on any selected dates.

While you can explore a more refined look using a custom Gantt chart visual, the matrix table can also achieve a basic Gantt-chart-like appearance, displaying the project duration and the team members.

I have attached an example pbix file for your reference.

Best regards,

[RichardLBHF1]

Hi,

Thank you for your reply. Originally I thought I may have been able to use this solution however unfortunately it does not quite work. The reasons are:

1) It does not like realtive dates.

2) If the start date is not before the first date it doesn't pick the person up,

3) If the end date is before the last date of the serch then the person isn't picked up.

Thank you for the effort though

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...