Forum Discussion

JamesGordon's avatar
JamesGordon
Icon for Helper II rankHelper II
4 years ago
Solved

Inventory Trend Analysis

Hi all, I’m sure there is a simple way to achieve this but I am struggling to achieve what I am after. I am trying to create bar chart that shows the amount of stock on hand today and historically. ...
  • johnt75's avatar
    johnt75
    4 years ago

    The basic formula is

    Stock Level = 
    var maxDate = MAX('Date'[Date])
    var minDate = MIN('Date'[Date])
    return CALCULATE( [Base Measure], 'Table'[Rcv Date] >= minDate && 'Table'[Rcv Date] <= maxDate &&
        ( 'Table'[Inv Date] > maxDate || ISBLANK('Table'[Inv Date] )
    )

    You don't need any relationships between your date table and data table for this to work.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

It sounds like there may be a filter being based from the Date table. Is there a relationship between the Date table and the data table? If there is you could add REMOVEFILTERS('Date') to the CALCULATE statement.