cancel
Showing results for
Did you mean:
Helper I

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Trying to create measure to display duration that company has been active

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

5 REPLIES 5
Super User I

## Re: Trying to create measure to display duration that company has been active

Hi @ccsrtw

Can you please share the output you are expecting.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Microsoft

## Re: Trying to create measure to display duration that company has been active

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

Helper I

## Re: Trying to create measure to display duration that company has been active

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!

Microsoft

## Re: Trying to create measure to display duration that company has been active

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

Helper I

## Re: Trying to create measure to display duration that company has been active

@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!

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors