Make a Chart with Order Status based on Create Date and Close Date, group by month

[jo_at_price]

I have a simple Orders table and I need to create a chart based on Order Status by month. All I have is a [create date] and [Closed date], I came up with a solution, but it's too cumbersome and I have to add a couple of new rows to a Historic table a the beginning of each month. Is there a better way? Here's a snapshot of the first 14 rows:

So I created some columns to get the number of orders open / closed June, July August, Sep:

I crreated a "Historic" table:

Historic = UNION(

SUMMARIZE('Orders', "New Status","Open", "hDate", date(2021,6,1), "hCount", SUM('Orders'[1. Open In June]))

, SUMMARIZE('Orders', "New Status","Open", "hDate", date(2021,7,1), "hCount", SUM('Orders'[2. Open In July]))

, SUMMARIZE('Orders', "New Status","Open", "hDate", date(2021,8,1), "hCount", SUM('Orders'[3. Open In August]))

, SUMMARIZE('Orders', "New Status","Open", "hDate", date(2021,9,1), "hCount", SUM('Orders'[4. Open In September]))

, SUMMARIZE('Orders', "New Status","Closed", "hDate", date(2021,6,1), "hCount", SUM('Orders'[5. Closed In June]))

, SUMMARIZE('Orders', "New Status","Closed", "hDate", date(2021,7,1), "hCount", SUM('Orders'[6. Closed In July]))

, SUMMARIZE('Orders', "New Status","Closed", "hDate", date(2021,8,1), "hCount", SUM('Orders'[7. Closed In August]))

, SUMMARIZE('Orders', "New Status","Closed", "hDate", date(2021,9,1), "hCount", SUM('Orders'[8. Closed In September]))

)

And the Chart works as expected:

 But is there a way to have that same Chart without a "Historic" table?

[amitchandak · Accepted solution]

@jo_at_price , Can it be solved using Open and close dates like HR?

Check if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

I think you need a measure like current employee