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.
Microsoft EmployeeYou 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.
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]))
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?
- kdejonge
well you did lose the count, if that is OK then that'll work.
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.
