Histogram of open items by month

[IntaBruce]

Hi, I want to create a histogram showing the total number and value of open orders by month.

This is NOT the number and value of orders placed or orders closed in each month but those that were still open at the end of the month, regardless of when they were placed.

My dataset has fields including: order_number, order_raised_date, order_fulfiled_date

Many thanks for your suggestions of how to achieve this.    Bruce

[IntaBruce]

Thank you @Greg_Deckler for your super quick response!

I have attempted to replicate your code with my table and field names inserted.  However the result was not quite as expected:

I struggled to follow the code so may have got something wrong but I'm pretty sure I don't have 49 open orders for the duration of the year.  Below is a sample of the data showing fulfillment dates in various months.

This is the measure:

Orders Open =

VAR tmpOrders = ADDCOLUMNS('salesorder',"Effective Date",IF(ISBLANK([Date Fulfilled]),TODAY(),[Date Fulfilled]))

VAR tmpTable =  

SELECTCOLUMNS(

    FILTER(

        GENERATE(

            tmpOrders,

            'Calendar'

        ),

        AND(

            [Date] >= [Date Fulfilled] && [Date] <= [Effective Date],

            NOT([Created On]=[Effective Date])

        )

    ),

    "ID",[(pk)Order],

    "Date",[Date]

)

VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))

RETURN COUNTROWS(tmpTable1)

Can you see my error?

[Greg_Deckler · Accepted solution]

@IntaBruce 
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

[IntaBruce]

Hi again @Greg_Deckler , I think I have fixed the code.  I needed to specify .[Date] after the field name and now it works (almost!  There is still a problem with open and closed on the same day, but I'm not actually sure if I need to count those)

Final measure and result below, thanks again for your post! 🙂