Calculate inventory on a given date

[Klarsen]

I've got a dataset consisting of some items that are continously added to the inventory and continously removed from the inventory. In this way each item has a start date and an end date (if removed) or the end date is empty. An example is given below. I want to create a graph with drill down where you on a given date can see how many items is on the inventory and by drilling down yoy can see the items. Any suggestions about how to accomplish this?

Item     Start_date     End_date

Item1   1/1-2015       30/4-2016

Item2   3/2-2015       2/2-2016

Item3   4/4-2016

Item4   5/1-2015       7/6-2015

[OwenAuger]

Hi @Klarsen,

Here is an approach using an 'events in progress' type measure which is appropriate when your table has start and end dates.
(see this paper page 27: http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf)

Here is a Sample PBIX file

The measure looks like this.

Notes:

• The measure will return the inventory as at the last date of the selected date range. For example, if you are browsing by month, the measure will return the inventory on the last date of the month.
• I have included a relationship between Inventory and Date tables, but the measure doesn't need to use this, and ignores it with the ALL( 'Date' ).

Inventory Count = 
SUMX (
    GENERATE (
        CALCULATETABLE (
            SUMMARIZE (
                Inventory,
                Inventory[Start_date],
                Inventory[End_date],
                "Rows", COUNTROWS ( Inventory )
            ),
            ALL ( 'Date' )
        ),
        INTERSECT (
            DATESBETWEEN ( 'Date'[Date], Inventory[Start_date], Inventory[End_date] ),
            LASTDATE ( 'Date'[Date] )
        )
    ),
    [Rows]
)

[BhaveshPatel]

Hi There,

Please see this file here for the solution.

https://drive.google.com/open?id=0B5-C_3XrFPdOV19uNHlVQmNvcG8

Thanks & Regards,

Bhavesh

If you like my post, please hit the "Kudo" Button and mark it as solution.