Need help combining two date fields

[rdorsey]

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!

[fhill · Accepted solution]

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

[rdorsey]

This worked perfectly, thank you!

[Greg_Deckler]

@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/409365