How to create duration for months based on multiple criteria

[PBI_newuser]

Hi,

I have 2 tables, one is Contract table and the other one is Service table.

I would like to create the duration in months from 1 to 12 based on the contract start date.

For example: 

Product A has a 3-year contract where contract start date is 1/1/2018 and contract end date is 31/12/2020.

The duration should stop at 12 only. After 12 months, the duration start from 1 again from 13th to 24th month of the contract.

Duration	Contract Year 1	Contract Year 2	Contract Year 3
1	Jan-18	Jan-19	Jan-20
2	Feb-18	Feb-19	Feb-20
3	Mar-18	Mar-19	Mar-20
4	Apr-18	Apr-19	Apr-20
5	May-18	May-19	May-20
6	Jun-18	Jun-19	Jun-20
7	Jul-18	Jul-19	Jul-20
8	Aug-18	Aug-19	Aug-20
9	Sep-18	Sep-19	Sep-20
10	Oct-18	Oct-19	Oct-20
11	Nov-18	Nov-19	Nov-20
12	Dec-18	Dec-19	Dec-20

In the Service table, we look at the Job Created Date and see which duration it falls within the contract. For example, if the job is created in Oct-18, then it should fall on duration 10, if the job is created in May-19, then it should fall on duration 5. Then we calculate the hours difference between Repair Date and Closed Date of each job. We wanna see if the hours difference is above or below the target line as shown in the example below.

Contract table:

Product	Contract Start Date	Contract End Date	Contract Duration (years)
AAA	01-02-19	31-01-20	1
BBB	04-07-18	03-07-20	2
CCC	20-01-17	19-01-21	4
DDD	15-06-20	14-12-20	0.5

Service table:

Job	Product	Job Created Date	Repair Date	Closed Date	Hours Diff	Expected Duration
Job-123	AAA	11-01-19 14:03	22-01-19 14:03	04-02-19 14:03	312
Job-124	AAA	20-05-19 23:04	01-06-19 23:04	13-06-19 23:04	288	4
Job-125	AAA	14-03-20 9:13	27-03-20 9:13	07-04-20 9:13	264
Job-126	AAA	06-07-19 4:08	20-07-19 4:08	30-07-19 4:08	240	5
Job-127	BBB	02-06-18 22:20	17-06-18 22:20	26-06-18 22:20	216
Job-128	BBB	06-10-18 10:10	22-10-18 10:10	30-10-18 10:10	192	4
Job-129	BBB	20-06-20 8:45	07-07-20 8:45	14-07-20 8:45	168	12
Job-130	BBB	03-04-20 16:23	21-04-20 16:23	27-04-20 16:23	144	10
Job-131	BBB	16-03-19 17:32	03-04-19 17:32	09-04-19 17:32	144	9
Job-132	CCC	12-01-17 10:43	31-01-17 10:43	05-02-17 10:43	120
Job-133	CCC	12-01-21 14:55	22-01-21 14:55	05-02-21 14:55	336	12
Job-134	CCC	04-08-19 22:27	06-08-19 22:27	28-08-19 22:27	528	3
Job-135	CCC	15-09-18 21:43	19-09-18 21:43	09-10-18 21:43	480	8
Job-136	CCC	26-02-17 13:32	04-03-17 13:32	22-03-17 13:32	432	2
Job-137	CCC	13-02-21 9:33	21-02-21 9:33	09-03-21 9:33	384
Job-138	CCC	14-07-19 19:30	31-07-19 19:30	07-08-19 19:30	168	7
Job-139	DDD	22-07-20 8:56	27-07-20 8:56	15-08-20 8:56	456	2
Job-140	DDD	10-12-20 12:40	11-12-20 12:40	03-01-21 12:40	552	6

Can anyone help on this? Sample data here.

Thanks.

[Anonymous]

Hi @PBI_newuser ,

Please replace

SWITCH ( TRUE (), _hour > 12, MOD ( _hour, 12 ), _hour )

with

IF(MOD(_hour,12)=0,12,MOD(_hour,12))

But sorry for that I'm a little confused about your last question.

I did not learn a lot about FLOOR(). Sorry again...

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_newuser]

Hi @Anonymous  , sorry for the confusion. I wish to create Cumulative Hours Diff based on the "Product_Year" and "Time(months)". Here is the pbix file.

For example,

In addition, how to create a column with generating a series as below?

Table = ADDCOLUMNS( GenerateSeries(1,12,1),"Cumulated Target",[Value]*200,

[Anonymous]

Hi @PBI_newuser ,

Sorry for my careless idea.

According to my understanding ,when [Job Created Date] is between [Contract Start Date] and [Contract End Date], return a remainder after a HourDiff is divided by a 12, otherwise, return blank, right?

Please try this:

Time(months) =
VAR _start =
    LOOKUPVALUE (
        'Contract_New'[Contract Start Date],
        'Contract_New'[Product], [Product]
    )
VAR _end =
    LOOKUPVALUE (
        'Contract_New'[Contract End Date],
        'Contract_New'[Product], [Product]
    )
VAR _hour =
    DATEDIFF ( _start, [Job Created Date], MONTH ) + 1
RETURN
    IF (
        [Job Created Date] >= [Contract Start Date]
            && [Job Created Date] <= [Contract End Date],
        SWITCH ( TRUE (), _hour > 12, MOD ( _hour, 12 ), _hour ),
        BLANK ()
    )

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_newuser]

Hi  , thank you so much for your great solution! I learned a lot from your solution.

Yes, it should be the remainder but if the remainder is 0, I would like to have it as 12.

Sorry to bother you, one last question, I learned how you created the new Table by using the earlier sample data. But with the current data, is it possible to a measure to plot the similar chart? E.g. "Product BBB Year 2" field will auto populate by using the FLOOR (_hour,12)?

[Anonymous]

Hi @PBI_newuser ,

1. Add Time(months) column:

Time(months) =
VAR _start =
    LOOKUPVALUE ( 'Contract'[Contract Start Date], 'Contract'[Product], [Product] )
VAR _end =
    LOOKUPVALUE ( 'Contract'[Contract End Date], 'Contract'[Product], [Product] )
VAR _hour =
    DATEDIFF ( _start, [Job Created Date], MONTH ) + 1
RETURN
    SWITCH ( TRUE (), _hour < 0, BLANK (), _hour > 12, _hour - 12, _hour )

2. Create the new table:

Table =
ADDCOLUMNS (
    GENERATESERIES ( 1, 12, 1 ),
    "Cumulated Target", [Value] * 200,
    "Product AAA Year 1",
        CALCULATE (
            SUM ( 'Service'[Hours Diff] ),
            FILTER (
                'Service',
                'Service'[Product] = "AAA"
                    && 'Service'[Time(months)] <> BLANK ()
                    && 'Service'[Time(months)] <= [Value]
            )
        ),
    "Product BBB Year 1",
        CALCULATE (
            SUM ( 'Service'[Hours Diff] ),
            FILTER (
                'Service',
                'Service'[Product] = "BBB"
                    && YEAR ( [Job Created Date] ) = 2019
                    && 'Service'[Time(months)] <> BLANK ()
                    && 'Service'[Time(months)] <= [Value]
            )
        ),
    "Product BBB Year 2",
        CALCULATE (
            SUM ( 'Service'[Hours Diff] ),
            FILTER (
                'Service',
                'Service'[Product] = "BBB"
                    && YEAR ( [Job Created Date] ) = 2020
                    && 'Service'[Time(months)] <> BLANK ()
                    && 'Service'[Time(months)] <= [Value]
            )
        )
)

The final output is show below:

But according to the screenshot, the  logic of Product BBB Year 1 column seems to be different from the other two columns(Product AAA Year 1 and Product BBB Year 2), Is it wrong or there is another logic?

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_newuser]

Hi @Anonymous , you are correct! The screenshot for Product BBB Year 1 is incorrect.

I noticed you created a new table for each Product-Year field. How can I create the same if I have different Contract Start date? When the Job Created Date is beyond the range of contract duration, how to ignore them in the calculation? Please see "Contract_New" and "Service"New" tables in the sample here.

I have created the same on "Service_New" table.

Thank you!! 

[Anonymous]

Hi @PBI_newuser ,

Not very clear. I'm quite confused about the logic of the Expected Duration column.

Could you please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business after removing sensitive data?

Best Regards,
Eyelyn Qin

[PBI_newuser]

Hi @Anonymous , sorry for the confusion. I have recreated the sample data here.

Please find the explanation in the below screenshot. The chart is expected output.

There are 2 sheets/tables to load into Power BI only.