Forum Discussion

Klarsen's avatar
Klarsen
New Member
9 years ago

Calculate inventory on a given date

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

Need Help

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    9 years ago

    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]
)