CODEWITHSUNDEEP
powerbidax
Deluctant RBA
Deluctant RBA

Reputation: 23

Using DAX to calculate future ON HAND inventory

I have 4 fields, how could I use DAX to achieve the following totals to create a graph:

   xxx = 10+2-1 = 11
   yyy = 11+2-1 = 12
   zzz = 12+2-1 = 13

currentOH OnOrder EstSold Month

----------- -------- -------- -----------

10 2 1 Jan

xxx 2 1 Feb

yyy 2 1 March

zzz

Upvotes: 0

Views: 478

Answers (1)

Giovanni Luisotto
Giovanni Luisotto

Reputation: 1400

You can use time intelligence functions to archive this, but you will need to have a proper date in the table. if your table is "by month" just create a date column with the first day of the month in order to use those functions, and connect it with the calendar table.

The final set of formulas could be something like this.

--Option 1 - won't produce the correct result if the value is already cumulative in the table
Inventory On Hand = SUM('MyTable'[currentOH])
Inventory On Order = SUM('MyTable'[OnOrder])
Inventory Est Sold = SUM('MyTable'[EstSold])

Inventory Est Total = [Inventory On Hand]+[Inventory On Order]-[Inventory Est Sold]

Inventory Est Cumulative Total = TOTALYTD([Inventory Est Total], 'CalendarTable'[DateCol]) 

--Option 2 - works with cumulative values
-- you need to change only [Inventory On Hand]
Inventory On Hand =
    CALCULATE (
        SUM('MyTable'[currentOH]), 
        LASTNONBLANK (
            'Date'[Date],
            CALCULATE ( SUM('MyTable'[currentOH]) )
        )
    )

Note that something more might be needed to make it work properly

Upvotes: 1

Related Questions