Orders Open Trend by Day

[Anonymous]

The data below in green is what I have in my table. 

I need to find a way to calculate how many orders were open on a specific date based on the date they were opened and closed. The expected output is below and I would like to do a trend. 

How can I accomplish this in Power BI?

[RickV_MIA]

This is Awesome thanks for this solution.

We are able to measure Open and Closed Orders by Date in SSAS

[RickV_MIA]

We can review by shop

[v-kkf-msft]

Hi @Anonymous ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[Anonymous]

not 100%, i implemented the suggestion and it does not return the results I am expecting. Let me get some data to share.

[v-kkf-msft]

Hi @Anonymous ,

Please create a new table:

Date = CALENDAR(MIN('Table'[Open Date]),MAX('Table'[Close Date]))

Then create measure:

Measure = 
SWITCH(
    TRUE(),
    MAX('Date'[Date]) < MAX('Table'[Open Date]), "NA",
    MAX('Date'[Date]) >= MAX('Table'[Open Date])
        && ( MAX('Date'[Date]) < MAX('Table'[Close Date]) 
                || MAX('Table'[Close Date]) = BLANK() ), "Open",
    MAX('Table'[Close Date]) <> BLANK()
        && MAX('Date'[Date]) >= MAX('Table'[Close Date]), "Close"
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is working great for the chart as shown above. Now how do I get a chart where I can count how many are open on any given day? 

[v-kkf-msft]

Hi @Anonymous ,

Try the following formula:

OpenCount = 
CALCULATE(
        DISTINCTCOUNT('Table'[Sales Order]),
        FILTER( 'Table', [Measure] = "Open" )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I am looking at the data now and the counts are not correct. 

The formula is only counting orders that were created on 8/13 and are open. It needs to also count the orders that were created prior to 8/13 and are still open. 

Here are my formulas:

[v-kkf-msft]

Hi @Anonymous ,

Please try the following formula:

OpenCount = 
CALCULATE(
        DISTINCTCOUNT('vSalesOrder_Holds'[SO]),
        FILTER( ALLSELECTED('vSalesOrder_Holds'[SO]), [Measure] = "Open" )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I appreciate all of the help with this but I am still not getting the expected results. This report runs each morning as a snapshot and tells me that there are anywhere from 200-400 open orders. The numbers I am getting are still not as expected. 

What I am trying to get to is the number of orders that were open on the date listed in the chart. I am not sure what info I can give here to help accomplish this. 

[v-kkf-msft]

Hi @Anonymous ,

These formulas work fine in my sample data. Not sure why you didn’t get the correct results. Could you please share your PBIX file? Or you can try to export the visual data into a CSV file, and see how many values are "open" in each Date.

Best Regards,
Winniz

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[mahoneypat]

Please see this DAX pattern for how to solve this one.

Events in progress – DAX Patterns

Pat

[Anonymous]

Thanks so much! I am trying it out now.