Filter legend in clustered bar chart by date

[iLoveTea]

Hello PowerBi Community,

I would like to have some help on a clustered bar chart im making.

Here's what i have :

- Mister A entered the company on the 1st of april 2005 and is still in the company today.

- Mister A was working for a client from the 01.04.2005 to 31.12.2007 (Invoiced) then stopped working for clients since 01.12.2009 (Not Invoiced).

What i'd like to have :

Mister A is shown as invoiced/not invoiced depending on the mission dates.

Additional informations :

The measure that calculates the number in the yellow background is the following :

_Cumul = CALCULATE( COUNTROWS('Append1'),

FILTER(VALUES('Append1'[Custom.DateEntree]),'Append1'[Custom.DateEntree]<=MAX('Date'[Date])),

FILTER(VALUES('Append1'[Sorties.Custom.DateSortie]),OR ('Append1'[Sorties.Custom.DateSortie]>MAX('Date'[Date]), ISBLANK('Append1'[Sorties.Custom.DateSortie]))))

The measure that calculates if someone is invoiced or not is the following :

isFacturable =

IF (

    ( 'Table finale'[qim-intranet missions.MIS_facturable] = "True" ),

    "Invoiced",

    IF (

        ( 'Table finale'[qim-intranet missions.MIS_facturable] = "False" ),

        "Not Invoiced",

        IF (

            ( 'Table finale'[Custom.Type ] = "Client" )

                || ( 'Table finale'[Custom.Type ] = "Réembauche_client" ),

            "Invoiced",

            IF (

                ( 'Table finale'[Custom.Type ] = "Direct" )

                    || ( 'Table finale'[Custom.Type ] = "Réembauche_ED" )

                    || ( 'Table finale'[Custom.Type ] = "Plateau" )

                    || ( 'Table finale'[Custom.Type ] = "Réembauche_plateau" )

                    || ( 'Table finale'[Custom.Type ] = "STAFF" )

                    || ( 'Table finale'[Custom.Type ] = "Réembauche_STAFF" ),

                "Not Invoiced",

                BLANK ()

            )

        )

    )

)

Thank you for your help !

[lbendlin]

What have you tried and where are you stuck?

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[iLoveTea]

@lbendlin 

Here is a data sample : 

Last Name	Name	Input Date	Output Date	Invoice Status	Mission Start Date	Mission End Date
Abc	def	01.04.2005		Invoiced	01.04.2005	31.12.2007
Abc	def	01.04.2005		Not Invoiced	01.12.2009
Uvw	Xyz	03.04.2017	01.12.2018	Invoiced	03.04.2017	01.12.2018

What i am trying to accomplish :

Bar chart showing the headcount of the company color coated by invoice status.

• By headcount i mean the number of employees in the company depending on their input and output date.

          It is done with the _Cumul measure on the first message.

• By Color coating for invoice status i mean that i want my bars to be in a blue color for invoiced employees and orange for not invoiced employees.

Basically, with this sample data i'd like to have one or two bars for each year from 2005 to 2023.

For ABC, the bar would be blue (invoiced) from 2005 to 2007 then empty until 2009 then orange (not invoiced) from 2009 to 2023.

For Xyz, no bars from 2005 to 2017 then the bar would be blue from 2017 to 2018 then empty.

In the backend i have :

Table A : One table with only input and outputs of employees 

Table B : One table with all the missions of the employees

Table C : For the timeframe use a calendar table derived from the input date of Table A

Links are made with a unique trigram derived from name and lastname of employees.

Thank you !

[lbendlin]

here's the transforms required to make this work.  You still need your table C (the calendar table).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxKVtJR8swry89MTk0BMg0M9QxM9IwMDEyBHGNDPUMjEMdcKVYHptgvv0QBVQNEjSWQowBWF1pWjmaoMcRQQ3MkDYYWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Last Name" = _t, #"Invoice Status" = _t, #"Mission Start Date" = _t, #"Mission End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mission Start Date", type date}, {"Mission End Date", type date}},"de"),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,Date.From(DateTimeZone.UtcNow()),Replacer.ReplaceValue,{"Mission End Date"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Date", each {Number.From([Mission Start Date])..Number.From([Mission End Date])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

See attached for a sample visual.

[iLoveTea]

@lbendlin 
Thank you for your help.

I managed to adapt your code to my report and it works alone but not with my _Cumul measure which is supposed to calculate the headcount of the company.
Here's is a picture of my model :

1. The Missions table is where i applied the modifications you provided.
2. The Employees table is where i calculate my measure of the headcount of the company using entry and exit of the company dates hence the two links to the date table.

3. The Date table is made with an autocalendar function.

I think the problem is due to the link between the Employees and Missions table, because my _Cumul measure use a Countrows function and the link fiddles with the number of lines in my employees table.
Do you know if there's a way to keep the link without it having an effect on my countrows function ? 

Thank you 

[lbendlin]

Correct, you don't usually want to have links/joins between fact tables.  Your Date table should have an active and an inactive join to the employees table, they should be single directional. There should not be a link between employees and missions.

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.