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.
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?
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.