Count Number Of Active Orders

This is perfect Sean, could I now take the extra favor of asking you to explain the last bit of

Table[START_DATE] <= LASTDATE ( CalendarTable[Date] ) &&

Table[END_DATE] >= FIRSTDATE ( CalendarTable[Date] )

Specifially how the LASTDATE and FIRSTDATE functions are being used here.

moizsherwani

Active Count is the count of the unique ORDER_IDs that were active at any time during a given period!

Active Orders could have:

1) started before the period

2) started and ended during the period OR

3) started during the period but not ended until later

So Active would mean START_DATE any time before the period ended && END_DATE any time after it started

Regarding the above solution you can read more here

http://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbell/

And also at the link on the bottom - Anonymous explains really really well! :smileyhappy: (after several cups of coffee :smileylol:)

Okay so truth be told - there's also another way to do this in a new table

(which may give you more flexibility depending on how much further analysis you'll be doing)

To try it click New Table on the Modeling tab...

Orders Table =
SUMMARIZE (
    GENERATE (
        'Table',
        CALCULATETABLE (
            VALUES ( 'CalendarTable'[Date] ),
            DATESBETWEEN ( CalendarTable[Date], 'Table'[START_DATE], 'Table'[END_DATE] )
        )
    ),
    'Table'[ORDER_ID],
    CalendarTable[Date]
)

Then create this simple distinct count Measure

Orders = DISTINCTCOUNT ( 'Orders Table'[ORDER_ID] )

And here we go...

You can see another example and read more about this and the other solution here...

http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregation/m-p/40705#M15311

Good Luck! :smileyhappy: