calculating from two date fields in one table

[mathcathy]

Hi,

I have a dataset which measures the start and end dates of many different activities.

To simplify, let's say three columns - Activity Type, Start Date, Completion Date

I want to create a new table with a count of the number of activities which are started and completed on each date.

So far, I've:

 - created a table with every date from the Start Date column and every activity type and then counted the number of rows with the correct start date and activity type into one column and the number of rows with the correct complete date and activity type into another. This didn't work because there were activities completed on dates where nothing started.

 - created a table with every date from the minimum start date to the maximum start date and counted the number of rows as above. This works to get the correct start and complete counts, but has no way to link to the activity type.

Can anyone suggest a way to get a table startpoint that has all the required dates associated to every possible activity type, please? (Or any alternate ideas to get the counts I'm looking for ...?)

Cathy

[kdejonge · Accepted solution]

You can create a calculated table to solve this.

The expression would be something like this:

Table = var tablevalstart = SUMMARIZECOLUMNS(Table1[Start date],Table1[Activity type],"count", COUNTROWS(Table1))
var tablevalend = SUMMARIZECOLUMNS(Table1[End date],Table1[Activity type],"count", COUNTROWS(Table1))
var fulltable = UNION(tablevalstart,tablevalend)
return fulltable

This would first count all the occurences by start dates and activity type, then will do the same for End date and in the end Union them together. Now when you put this in a table or visual you will the sum of both each day by activity type.  

You can also do this using PowerQuery but for me DAX is faster :). Hope this is what you are looking for.

[mathcathy]

I used:

Table = var tablevalstart = SUMMARIZECOLUMNS(Table1[Start date],Table1[Activity type],"count", COUNTROWS(Table1))
var tablevalend = SUMMARIZECOLUMNS(Table1[End date],Table1[Activity type],"count", COUNTROWS(Table1))
var fulltable = summarize(UNION(tablevalstart,tablevalend), [Start date], [Activity type])
return fulltable

This works. Is it a good way to have done it?

[kdejonge]

well you did lose the count, if that is OK then that'll work.

[mathcathy]

That works, because I needed the counts separately in order to subtract the completed items from the started items and get a picture of how much the queue size for each activity changes each day.

Those three columns were also a simplification of the original data, which has a couple of other columns which make things a little more complex.

Thank you so much for your help. I'd been puzzling on this one for a week or more.

[mathcathy]

Thank you. It's very very nearly what I need, except that the union query includes duplicates. Is there a way to join the two queries together which excludes duplicate dates and activity types?

[kdejonge]

well if you put this data in a table or visual you will not see the duplicated data, it will be grouped automatically. 

But if you really want it grouped you can create a new table with something like this:

Table 2 = SUMMARIZECOLUMNS('Table'[Activity type],'Table'[Start date],"count",SUM('Table'[count]))