Forum Discussion
Solved
Need help combining two date fields
I have a data set which has fields for "process name", "date started", and "date completed". I would like to create a clustered column chart with time on the X axis and for the Y axis a column showin...
OK, probably lots of ways to do this, but here's one idea...
1. Create a new Calendar Date Table based on the MIN Start Date and MAX Complete Date:
zDate Table = CALENDAR(MIN('zData Table'[Date Started]), MAX('zData Table'[Date Complete]))** Don't join it to your original table, i'll explain why later. **
Create these custom columns on your NEW Date table...
Count of Start Dates = CALCULATE( COUNTA( 'zData Table'[Date Started]), FILTER('zData Table', 'zData Table'[Date Started] = 'zDate Table'[Date]))Count of Comp Dates = CALCULATE( COUNTA( 'zData Table'[Date Complete]), FILTER('zData Table', 'zData Table'[Date Complete] = 'zDate Table'[Date]))Now create your chart based on the Date Table and your 2 new columns....If you create a Join between the two tables, you would have to choose a primary connection between either Date Started or Date Completed, and that would throw off all your numbers.Hope this helps...Forrest
OK, probably lots of ways to do this, but here's one idea...
1. Create a new Calendar Date Table based on the MIN Start Date and MAX Complete Date:
zDate Table = CALENDAR(MIN('zData Table'[Date Started]), MAX('zData Table'[Date Complete]))
** Don't join it to your original table, i'll explain why later. **
Create these custom columns on your NEW Date table...
Count of Start Dates = CALCULATE( COUNTA( 'zData Table'[Date Started]), FILTER('zData Table', 'zData Table'[Date Started] = 'zDate Table'[Date]))
Count of Comp Dates = CALCULATE( COUNTA( 'zData Table'[Date Complete]), FILTER('zData Table', 'zData Table'[Date Complete] = 'zDate Table'[Date]))
Now create your chart based on the Date Table and your 2 new columns....
If you create a Join between the two tables, you would have to choose a primary connection between either Date Started or Date Completed, and that would throw off all your numbers.
Hope this helps...
Forrest
This worked perfectly, thank you!