Forum Discussion

JamesGordon's avatar
JamesGordon
Helper 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
Super User
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.

JamesGordon's avatar
JamesGordon
Helper II
4 years ago

johnt75 

Thank you for your help. I have tried your formula and it appears to be getting close. Something isnt quite working through. 

If i put the results into a table i get the figure i would expect 10,980,463.41 for todays stock level - however when i try to put into a bar graph the 2022 stock level is only showing 4,302,876.35

I cannot see why this would be the case? as per below?