cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ccsrtw Helper I
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:

 

DateCustomer NumberProduct NumberCustomer DurationProduct Duration
10/1/2016100501001712
9/1/2016100501001611
8/1/2016100501001510
7/1/201610050100149
6/1/201610050100138
5/1/201610050100127
4/1/201610050100116
3/1/201610050100105
2/1/20161005010094
1/1/20161005010083
12/1/20151005010072
11/1/20151005010061
10/1/20151005020055
9/1/20151005020044
8/1/20151005020033
7/1/20151005020022
6/1/20151005020011

 

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
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.

t1.PNG

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

result.PNG

 

Regards

View solution in original post

5 REPLIES 5
Super User I
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
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?Smiley Happy

 

Regards

ccsrtw Helper I
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
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.

t1.PNG

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

result.PNG

 

Regards

View solution in original post

ccsrtw Helper I
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!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors