cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YorickStev1991
Regular Visitor

Count of Active Contracts by Start en End date

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

2 REPLIES 2
BhaveshPatel
Community Champion
Community Champion

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 ColumnConditional Columnactivecontract2.PNGactivecontract1.PNG 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
ImkeF
Super User II
Super User II

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!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.