Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help needed with DAX...Please help....

I have two tables in Power BI Receivor table and Inventory table. I have a slicer for date field. This field, i get from calendar table.

 

Now, my requirement is if i select a date in slicer 2021-Apr-24, then I have to see how much quantity is left out in the inventory. For example, from the inventory received table,

there are total 60 quantity that has been received so far but only 35 has been sent (details are in inventory table), so there are remaining 25 quantity left out in the inventory on 24th april 2021.

so, when i click on slicer i need to see a column which shows total stock left out in the inventory table after sending to receivor.

 

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Calendar must be a date table in the model that's been marked as such in order for the formula to work.

     

    // Your model should be a star-schema with
// conformed dimensions. If it's not, then
// the DAX will always have problems of some
// sort. Please create correct models to
// avoid mistakes. Google for "star schema
// in Power BI" to learn about what a good
// model is.

// Assuming that Calendar is connected to
// both tables on the corresponding date fields
// and the relationships are one-to-many with
// one-way filtering.

[Total Left] =
var vLastVisibleDate = MAX( 'Calendar'[Date] )
var vLastVisibleQty =
    CALCULATE(
        SUM( 'Inventory Table'[Quantity] )
         - SUM( 'Receivor Table'[Quantity] ),
        Calendar[Date] <= vLastVisibleDate
    )
RETURN
    vLastVisibleQty