create one report based on customers valid characteristics between start and end date

[URobert]

Hi,

Please help me with the following issue.

I have a 3 different tables with different informations about the customers but each one has a time slice.

I want to create one table with all the informations based on customer name as an unique ID between tables which will show me on line level each customer with valid characteristic for that period (maybe on daly / month / year split).

Or to have a slicer as a period and to show me all the valid characteristics for each customer (not only one one line per customer) each valid characteristic between selected period.

input data table 1

customer	contract ID	contat starting date	contract end date
A	12	01.01.2017	31.12.2018
A	35	01.01.2018	31.12.2020
B	2	01.01.2018	01.07.2018
C	4	15.04.2019	10.03.2020
A	135	01.06.2018	31.12.2019

input data table 2

customer	customer characteristic	contat starting date	contract end date
A	Large	01.01.2016	01.10.2017
A	Medium	02.10.2017	01.02.2018
A	Small	02.02.2018	31.12.2019
A	Medium	01.01.2020	30.06.2020
A	Extra Large	01.07.2020	31.10.2020
B	Small	01.01.2018	01.01.2019
C	Extra Large	01.01.2020	31.12.2021
B	Medium	02.01.2019	01.06.2020

input data table 3

customer	internal/external	contat starting date	contract end date
A	Internal	01.01.2016	01.11.2017
A	External	02.11.2017	01.02.2018
A	Internal	01.01.2020	30.06.2020
A	External	01.07.2020	31.10.2020
B	Internal	01.01.2018	01.01.2019
C	Internal	01.01.2020	31.12.2021
B	External	02.01.2019	01.06.2020

result 1 (example only for customer A)

customer	start valid date	end valid date	contract id	customer characteristic	internal/external
A	01.01.2017	01.10.2017	12	Large	Internal
A	02.10.2017	01.11.2017	12	Medium	Internal
A	02.11.2017	31.12.2017	12	Medium	External
A	01.01.2018	31.01.2018	35	Medium	External
A	01.02.2018	01.02.2018	35	Medium
A	02.02.2018	31.12.2019	35	Small
A	01.01.2020	30.06.2020	35	Medium	Internal

result 2 (example only for customer A)

customer	valid date	contract id	customer characteristic	internal/external
A	01.01.2017	12	Large	Internal
A	02.10.2017	12	Medium	Internal
A	02.11.2017	12	Medium	External
A	01.01.2018	35	Medium	External
A	01.02.2018	35	Medium
A	02.02.2018	35	Small
A	01.01.2020	35	Medium	Internal

result 3 (example only for customer A)

customer	year	contract id	customer characteristic	internal/external
A	2017	12	Large	Internal
A	2017	12	Medium	Internal
A	2017	12	Medium	External
A	2018	35	Medium	External
A	2018	35	Medium
A	2018	35	Small
A	2020	35	Medium	Internal

result 4 (example only for customer A)

customer	date	contract id	customer characteristic	internal/external
A	01.01.2017	12	Large	Internal
A	02.01.2017	12	Large	Internal
A	03.01.2017	12	Large	Internal
A	04.01.2017	12	Large	Internal
A	……	……	……	……
A	01.10.2017	12	Large	Internal
A	02.10.2017	12	Medium	External

Or other ideas to be able to see customers based on their valid characteristics on a certain date (day / month or year) or for a certain period selected in a slicer.

Thank you !

[mahoneypat]

Here is how I would approach this one.

1.  Bring in all 3 tables, and disable load (right click each and uncheck Enable Load).  Add a custom column called "Attribute" to each query with the respective type of information ( = "Contract", "Size", "Internal/External").  Also rename the ContractID, Internal/External, and Customer Characteristic columns to "Value".

2.  Append all 3 tables, and call the table "Customer".  This should result in one table with 5 columns and all your customer information.

3.  You can then make a matrix visual with Customer on rows, Attribute on columns, and the measure below in values.

4.  Add a Date table but have no relationship to your customer table.

5.  Add a Date slicer (single date for this example)

6.  Have a measure like

Customer Value =
VAR thisdate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        MIN ( Customer[Value] ),
        FILTER (
            Customer,
            Customer[StartingDate] <= thisdate
                && Customer[EndingDate] >= thisdate
        )
    )

This should show you the characteristic for each attribute on that date for each customer.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Fowmy]

@URobert 

You only have Customer as to the common column across tables, how do you relate Customer Large from Table 2 to Table 1? Against which row. Clarify with that logic, please.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[URobert]

Hi,

The result i want to get is a table on customer level, with valid characteristic based on selected date.

If a have a period selected and for one customer i have different characteristics, to have different lines for the same customer.

So the customer name is the unique id to link the tables and maybe i need to create a data table in order to show the valid characteristic from each table and to link it with the customer.

[Anonymous]

I could be wrong but I don't think it can be done. As you have nothing unique for each row/customer, you're going to experience a lot of duplication each time you merge each data set. 

for example, there's no way for it to distinguish that customer ID (12) from "input table 1" related directly to customer characteristic "Large" from input table 2. So you'll get 3 or 4 duplicates with that merge alone.