Sum from other table when previous row is smaller than the following row

[reggieneo]

Hello again,

Kindly assist with this: I would like to sum sales when the previous row is smaller than the row below it.

in the image attached, 3479 is smaller than the immediate row below it 5361 so the expression should calculate the sum.

Sum(SELECT(
Sales[Diesel Liter Sales],
[Timestamp] >= MAX(
SELECT(
Sales[Timestamp],
([Diesel Opening Dipstick] >[Most Recent].[Diesel Opening Dipstick] )))))

my expression is giving me an error of unable to find the column “[Diesel Opening Dipstick]” of [Most Recent].[Diesel Opening Dipstick].
thanks.

[Steve]

Your request is unclear, but here’s something:

IFS(
  (
    LOOKUP(
      MAX(
        SELECT(
          Sales[_ROWNUMBER],
          ([_THISROW].[_ROWNUMBER] > [_ROWNUMBER])
        )
      ),
      "Sales",
      "_ROWNUMBER",
      "Diesel Liter Sales"
    )
    <
    [Diesel Liter Sales]
  ),
  (
    LOOKUP(
      MAX(
        SELECT(
          Sales[_ROWNUMBER],
          ([_THISROW].[_ROWNUMBER] > [_ROWNUMBER])
        )
      ),
      "Sales",
      "_ROWNUMBER",
      "Diesel Liter Sales"
    )
    +
    [Diesel Liter Sales]
  )
)

[reggieneo]

Hi Steve,
sorry for not being clear.

I am trying to sum all Diesel Liter Sales from Sales Table when the immediate row below value is higher than its above row value. The idea is I want to start summing when there is an increase in stock due to new delivery.

the summing point of Diesel Liter Sales begins at the row that is parallel with the bigger value (5361) which is when the new stock arrives.

hope I make it clearer.

much thanks

[Steve]

Where would the answer be saved?

[reggieneo]

Hi Steve,
From your suggested expressions, I have temporarily got an accurate result by creating it in VC and a VC for the previous row that deducting the previous from the total sum.

the challenge is still to save in my Order Table and also stop the sum when a new immediate row below value is higher than its above row value.

IFS(
(
LOOKUP(
MAX(
SELECT(
Sales[_ROWNUMBER],
([_THISROW].[_ROWNUMBER] > [_ROWNUMBER])
)
),
“Sales”,
“_ROWNUMBER”,
“Diesel Liter Sales”
)
<
[Diesel Liter Sales]
),
(
LOOKUP(
MAX(
SELECT(
Sales[_ROWNUMBER],
([_THISROW].[_ROWNUMBER] > [_ROWNUMBER])
)
),
“Sales”,
“_ROWNUMBER”,
“Diesel Liter Sales”
)
+
Sum(Select(Sales[Diesel Liter Sales],[Timestamp]>=[_Thisrow].[Timestamp]))
)
)-[Previous Diesel Liter Sales]

[Steve]

While this looks like an interesting academic challenge, I don’t see any good way to do what you want. I encourage you to consider alternative approaches.

[reggieneo]

I have another Table (Order[Diesel Liter Sales]

[reggieneo]

Thanks Steve for your time in it… I have considered doing it thru an automatic action. so that i could always check the new delivery if arrives and get the calculation immediately than looking for a needle in haystack. glad to practice though and squeezed drain my brain.