Forum Discussion
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 showing how many processes were started and a column showing how many processes were ended. I can do this in two separate visualizations no problem, but my boss wants them in a single plot and I'm really at a loss for how to get that to work. Any advice would be greatly appreciated!
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
3 Replies
rdorsey If I am not mistaken, you are going for this:
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365OK, 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...ForrestThis worked perfectly, thank you!
