Compare dates between 2 tables to calculate the active products

[PBI_newuser]

Hi, I have a trend chart below to show how many hours spent in the month. I would like to see the average hours spent per active products. To calculate the active products, I need to compare the "Date" in TableA with the month in TableB.

The product is considered active if the

• "Date" > "Job Date"
• Month of "Date" - Month of "Job Date" <=36 months

For example, in Oct-20, total hours = 11. The difference months between "Date" and Oct-20 for A1 is more than 36 months. Hence the total active products in Oct-20 is 5. The average hours = 11/5 = 2.2 hours.

In Nov-20, total hours = 7. The difference months between "Date" and Nov-20 for A1 and A2 is more than 36 months. Hence the total active products in Oct-20 is 4. The average hours = 7/4 = 1.75 hours.

How to combine the information of these 2 tables to get the expected result?

TableA:

Product	Serial Number	Date
P-1	A1	05/04/2017
P-1	A2	11/13/2017
P-2	A3	12/22/2019
P-2	A4	02/10/2021
P-2	A5	04/19/2021
P-1	A6	09/15/2022

TableB:

Job Number	Job Date	Product	Labor Hours	Travel Hours	Total Hours
Job-123	10/14/2020	P-1	10	1	11
Job-234	11/04/2020	P-1	5	2	7
Job-345	01/11/2021	P-2	8	4	12
Job-456	03/12/2021	P-2	6	2	8
Job-567	04/17/2021	P-2	4	5	9
Job-678	05/04/2021	P-1	8	1	9

[v-easonf-msft]

Hi, @PBI_newuser 

Can you explain in detail, how did you calculate the total number of active products on October-20? I still don't understand why the total active products is 5.

Best Regards,
Community Support Team _ Eason

[PBI_newuser]

Hi @v-easonf-msft , below is an example how to count active products.

In month of Job Date from table B = Oct 2020, you may see only one serial number is more than 36 months, hence it's an expired product. The total active products in Oct 2020 = 5.

In Nov 2020, there are 2 serial numbers expired (more than 36 months), hence the total active products = 4.

Product	Serial Number	Date	YearMonth	Job Month (from TableB)	Months Diff (Oct-2020)	Job Month (from TableB)	Months Diff (Nov-2020)
P-1	A1	05/04/2017	2017-05	2020-10	42	2020-11	43
P-1	A2	11/13/2017	2017-11	2020-10	36	2020-11	37
P-2	A3	12/22/2019	2019-12	2020-10	11	2020-11	12
P-2	A4	02/10/2021	2021-10	2020-10	13	2020-11	14
P-2	A5	04/19/2021	2021-04	2020-10	7	2020-11	8
P-1	A6	09/15/2022	2022-09	2020-10	-24	2020-11	-23

[v-easonf-msft]

Hi, @PBI_newuser 

You need to create a summarize tableA to calculate the active product for each date in table B.

Active products = 
VAR tab =
    SUMMARIZE (
        'Table A',
        'Table A'[Date],
        'Table A'[Serial Number],
        "diff_month",
            DATEDIFF (
                SELECTEDVALUE ( 'Table A'[Date] ),
                SELECTEDVALUE ( TableB[Job Date] ),
                MONTH
            ) + 1   //here need add 1 month
    )
RETURN
    COUNTROWS ( FILTER ( tab, [diff_month] <= 36 ) )

The average hours = SELECTEDVALUE(TableB[Total Hours])/[Active products]

Best Regards,
Community Support Team _ Eason

[PBI_newuser]

Hi @v-easonf-msft , thanks for the solution! Usually TableA and TableB has few rows for the same month. For example, I added a new Job Number (Job-444) to TableB and the total hours in 2020-10 is 20 hours. But the active products became 6 instead of 5 and the average hours show empty. Could you please help on this? Thank you so much! 🙂 

Sample here.

[v-easonf-msft · Accepted solution]

Hi, @PBI_newuser 

You need to add additional fields ‘Job Number ’ into the visual object to make it display correctly.

You can also try the following formulas:

If your expected result of active products is 5, add a new formula as below:

Active products_2.2 = 
VAR tab =
    SUMMARIZE (
        TableB,
        TableB[Month],
        TableB[Job Number],
        "Active products2",TableB[Active products])
return Averagex(tab,[Active products2])

If your expected result of active products is 10(5+5),add a new formula as below:

Active products_2.1 = 
VAR tab =
    SUMMARIZE (
        TableB,
        TableB[Month],
        TableB[Job Number],
        "Active products2",TableB[Active products])
return SUMX(tab,[Active products2])

The calculation of average hours is the same.

Best Regards,
Community Support Team _ Eason