Trying to create measure to display duration that company has been active

[ccsrtw]

I have a large monthly billing file dating back to January '15 - each row is a monthly billing record for a customer's product.

Example:

Date	Customer Number	Product Number	Customer Duration	Product Duration
10/1/2016	10050	100	17	12
9/1/2016	10050	100	16	11
8/1/2016	10050	100	15	10
7/1/2016	10050	100	14	9
6/1/2016	10050	100	13	8
5/1/2016	10050	100	12	7
4/1/2016	10050	100	11	6
3/1/2016	10050	100	10	5
2/1/2016	10050	100	9	4
1/1/2016	10050	100	8	3
12/1/2015	10050	100	7	2
11/1/2015	10050	100	6	1
10/1/2015	10050	200	5	5
9/1/2015	10050	200	4	4
8/1/2015	10050	200	3	3
7/1/2015	10050	200	2	2
6/1/2015	10050	200	1	1

I filled out the two columns I want to create a measure for (Customer Duration & Product Duration). The file has thousands of different customers so that will need to be the lookup value I suppose, but how do I account for the time factor? I've linked a calendar table's Date Key to the Date in the above table if that helps.

Thanks!

[ccsrtw]

I'm looking for two outputs - Customer Duration and Product Duration. I populated the table with the values I want outputted.

I suppose by time factor I'm referring to the dates - need the durations to have some sort of time intelligence where they count only the rows with a date prior to its own. I hope that clears it up a bit, please let me know otherwise!

Appreciate the help!

[v-ljerr-msft · Accepted solution]

Hi @ccsrtw,

In this scenario, you should be able to use DATEDIFF Function (DAX) to calculate the time duration. See my sample below.

I assume you have a table called "MyTestTable" like below.

Then you should be able to use the formula below to create "Customer Duration" and "Product Duration" column.

Customer Duration = 
VAR cNum = MyTestTable[Customer Number]
RETURN
    DATEDIFF (
        CALCULATE (
            MIN ( MyTestTable[Date] ),
            FILTER ( ALL ( MyTestTable ), MyTestTable[Customer Number] = cNum )
        ),
        MyTestTable[Date],
        MONTH
    )
        + 1

Product Duration = 
VAR cNum = MyTestTable[Customer Number]
VAR pNum = MyTestTable[Product Number]
RETURN
    DATEDIFF (
        CALCULATE (
            MIN ( MyTestTable[Date] ),
            FILTER (
                ALL ( MyTestTable ),
                MyTestTable[Customer Number] = cNum
                    && MyTestTable[Product Number] = pNum
            )
        ),
        MyTestTable[Date],
        MONTH
    )
        + 1

Regards

[ccsrtw]

@v-ljerr-msft, that's exactly it! Thank you!

Another wrinkle that I discovered while working with the new formulas - sometimes a customer will skip a few months of billing. The way the formula is currently setup, those gaps still add up to the total duration. That is fine in the majority of cases, but I'm wondering if there is a way to adjust the formula to account for the gaps? In other words, any months with a billing prior to a gap would not count towards the duration.

Appreciate the help, this is great!

[v-ljerr-msft]

Hi @ccsrtw,

What do you mean about "how do I account for the time factor"? Could you be more precisely with this issue and post the expected result in your scenario?

Regards

[CheenuSing]

Hi @ccsrtw

Can you please share the output you are expecting.

Cheers

CheenuSing