cancel
Showing results for
Search instead for
Did you mean:
Post Prodigy

## Compare dates between 2 tables to calculate the active products

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
1 ACCEPTED SOLUTION
Community Support

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

5 REPLIES 5
Community Support

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

Post Prodigy

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
Community Support

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

Post Prodigy

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.

Community Support

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

## Helpful resources

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

#### Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors
Top Kudoed Authors