cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI_newuser
Post Prodigy
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?

 

PBI_newuser_0-1634703351638.png

 

TableA:

ProductSerial NumberDate
P-1A105/04/2017
P-1A211/13/2017
P-2A312/22/2019
P-2A402/10/2021
P-2A504/19/2021
P-1A609/15/2022

 

TableB:

Job NumberJob DateProductLabor HoursTravel HoursTotal Hours
Job-12310/14/2020P-110111
Job-23411/04/2020P-1527
Job-34501/11/2021P-28412
Job-45603/12/2021P-2628
Job-56704/17/2021P-2459
Job-67805/04/2021P-1819
1 ACCEPTED SOLUTION

Hi, @PBI_newuser 

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

130.png

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.

131.png

Best Regards,
Community Support Team _ Eason

View solution in original post

5 REPLIES 5
v-easonf-msft
Community Support
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

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.

 

ProductSerial NumberDateYearMonth

Job Month

(from TableB)

Months Diff

(Oct-2020)

Job Month

(from TableB)

Months Diff (Nov-2020)
P-1A105/04/20172017-052020-10422020-1143
P-1A211/13/20172017-112020-10362020-1137
P-2A312/22/20192019-122020-10112020-1112
P-2A402/10/20212021-102020-10132020-1114
P-2A504/19/20212021-042020-10 72020-118
P-1A609/15/20222022-092020-10-242020-11-23

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]

125.png

 

Best Regards,
Community Support Team _ Eason

 

 

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.

 

PBI_newuser_0-1634890051235.png

 

Hi, @PBI_newuser 

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

130.png

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.

131.png

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors