Forum Discussion
calculating from two date fields in one table
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]))