cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IntaBruce
Helper I
Helper I

Histogram of open items by month

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

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
IntaBruce
Helper I
Helper I

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:

IntaBruce_0-1664482865768.png

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.

IntaBruce_1-1664483186989.png

 

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
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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! 🙂 

IntaBruce_0-1664530653464.png

IntaBruce_1-1664530688680.png

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.