Forum Discussion
calculating from two date fields in one table
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
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 fulltableThis 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.
6 Replies
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 fulltableThis 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.
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?
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]))
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 fulltableThis works. Is it a good way to have done it?
well you did lose the count, if that is OK then that'll work.
