Forum Discussion

KatieH's avatar
KatieH
Advocate IV
8 years ago

Historic Live Data

Hi all,

 

Reaching out for some more help....

 

I have a huge data set about all contracts our company has which shows, amongst other things the start date and end date of each contract. I want to create an area chart visual which shows the live fleet growth month on month for the past few years. 

 

The only way I can think of to do this is to create columns on the data set for each and every month with a calculation determining whether each contract was live at that point (e.g. Jan 17 = start date < 01/02/17 and end date > 31/01/17 (or null)). However that would involve creating upwards of 36 columns etc. 

 

Is there any other way to do this?

Need Help

3 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    If you think that the size of your fleet is the number of contracts that have started but not finished, you could look to make a count of the number of contracts that have started before a given day, and then the number that have finished before that same day, and then subtract one from the other? This should then plot against a time series

    • KatieH's avatar
      KatieH
      Advocate IV
      8 years ago

      Thanks for replying jthomson, sorry it's taken me so long to reply.

       

      Apologies but I don't really know what you mean. Do you mean to perform that count for each month? I'm trying to avoid having to create 36+ additional columns in order to get a figure for each month.

       

       

      • jthomson's avatar
        jthomson
        Solution Sage
        8 years ago
        wrote:

        Thanks for replying jthomson, sorry it's taken me so long to reply.

         

        Apologies but I don't really know what you mean. Do you mean to perform that count for each month? I'm trying to avoid having to create 36+ additional columns in order to get a figure for each month.

         

         

        Not really, I'm suggesting that you have a date table, then create a measure which counts the number of rows in your main data table where the start date is before any given date in your date table, kind of like a running total. If you do the same for the end date, then you can take one from the other and get the number of active contracts at any given time - an active contract being one that has started, but hasn't finished. You'd then be able to put the date series in your date table as the x-axis in a graph, and your started contracts - finished contracts column/measure as the y-axis.

         

        This'd be continuous for any day, if you wanted just the month end value you could customise your date table to just include the last day of each month, or play with PBI's inbuilt date hierarchy and drop down as far as the month level and see if it'll output the last value.