Forum Discussion

jce0's avatar
jce0
Regular Visitor
7 years ago

Calculating License Renewal Statistics

First time working with PowerBI/DAX -I'm sure I'm overlooking something simple, but I've messed this up a few times while following along with some similar posts so I figured it was time to reach out...
Need Help
jce0's avatar
jce0
Regular Visitor
7 years ago

Thanks Greg. Those links are close to what I'm looking for, but not exactly. Let me add some detail to my original post with your suggestions.

 

Summary:

I have a single table where each row contains a bunch of information about customers. I think the relevant columns in this calculation are the licenseStartDate, licenseEndDate, and licenceType. I'd like to create a dataset and some visuals that indicate how the length (number of days) that a customer has had an active license changed over time, on average.

 

Sample Data:

https://drive.google.com/file/d/1kxiZ31Ff4H6HT9y4GbJZFa2f8kzY6AX_/view?usp=sharing

 

Description:

ID: Unique identifier of the customer

licenseStartDate: The date that the license was activated

licenseEndDate: The date that the licenses expired/was canceled.

licenseType: Indicates what type of license this record is related to

 

Expected Result:

I'm attempting to generate a graph where the X axis are dates for which licenses were created and the y axis is the number of days, on average, those licenses remained active. Possibly grouping the data on the X axis into months or quarters.

 

Thanks for the help!

 

jce0's avatar
jce0
Regular Visitor
7 years ago

Would anyone be able to help with this additional information? I think it could be helpful to others since I couldn't find any other post directly addressing this metric. Thanks!

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi jce0,

    Could you please offer me more logic information about how to calculate "the averge number of days that a license remained active" that you want?

     

    Regards,

    Daniel He

    • jce0's avatar
      jce0
      Regular Visitor
      7 years ago

      The number of days that a license remains active should just be (licenseEndDate) - (licenseStartDate) providing the number of days that a single license was active. I was then hoping to put that information into buckets (I was thinking by the year and month of the licenseStartDate) to examine how the length that licenses remained active changed over time.

       

      Let me know if that provides enough info.

       

      Thanks Daniel!

      • v-danhe-msft's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi jce0,

        Based on my test, you could refer to below formula:

        Days = DATEDIFF('Table1'[licenseStartDate],'Table1'[licenseExpirationDate],DAY)

        And you could use the [licenseStartDate] as x-axis and use the Days as value to create a visual:

        You could also download the pbix file to have a view.

         

        Regards,

        Daniel He