Count of Active Contracts by Start en End date

[YorickStev1991]

Hi,

Am struggling with showing my data by active contracts
I have the following data and it should show the count of contracts perferibly day by day

If the 'DateEndDeliver' is empty the contract is still running and is considerd active.

Input Table 'TblEGSales'

No contract     DateStart    DateEnd

A1111              13-10-13

B2222              13-05-14    13-06-14

C3333              14-05-14    13-07-14

Disered output:

13/10/2013 : 1

14/10/2013 : 1

...

12/05/2014 : 1

13/05/2014 : 2

14/05/2014 : 3

...

12/06/2014 : 3

13/06/2014 : 2

14/06/2014 : 2

Hoping that someone can help.

Thanks in advance Yorick

[BhaveshPatel]

Hi Yorick,

You can create a conditional column in Query Editor. This will evaluate Date End column. If the Date End column equals null, then 1 otherwise 0.

Load your query, Drag Date start column into the table visualization and activecontract column as well.

See the attached screenshot.

Conditional Column 

[ImkeF]

Edit: Check out the newest take here: http://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

This is "Event-in-Progress", check out the sources from this post: https://community.powerbi.com/t5/Desktop/Open-Tickets-per-group-by-date/td-p/9047

You might want to replace the empty field in the DateEnd in the query editor with todays date in order to make the DAX-calculation simpler.

You could also solve this task in the query editor altogether (espcecially if you have no calenar-table) like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQEAiUdpUMLDI11DQ10DY2BHKVYnWglJyMggMkYmOoamsA5ZiAOSI2zMRBAhE3Q1JiD1cQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"No contract" = _t, DateStart = _t, DateEnd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No contract", type text}, {"DateStart", type date}, {"DateEnd", type date}}),
    ReplaceEmptyWithToday = Table.ReplaceValue(#"Changed Type",null,Date.From(DateTime.LocalNow()),Replacer.ReplaceValue,{"DateEnd"}),
    CreateListOfAllDates = Table.AddColumn(ReplaceEmptyWithToday, "AllDays", each {Number.From([DateStart])..Number.From([DateEnd])}),
    ExpandAllDays = Table.ExpandListColumn(CreateListOfAllDates, "AllDays"),
    ChangeToDateFormat = Table.TransformColumnTypes(ExpandAllDays,{{"AllDays", type date}})
in
    ChangeToDateFormat

This will return with a table that has one row per day in each contract. You can create your table with "AllDays" for the dates and then a count on every other field would return the desired results.

But this would be much slower than the DAX-version for large datasets!