DAX Running total in Direct Query, but reset to zero each day, if running below zero

[PatrickS-DK]

Hi Power BI Community,

I have an issue, where I want to calculate a running total in a measure, so I'm able to get the primo and ultimo value of the stock quantity. This is a forecast of the stcok 14 days ahead of time, and to be sure that we won't run below zero on a given day. In the event of the running total going below zero, I need it to reset and use the 0 as the new starting point.

Backstory

I need to use Direct Query as the data source connection. I have therefore limitations that I cannot use a calculated column, since I cannot use CALCULATE in calculated columns due to Direct Query. I have no possibility to throw the calculation out of DAX and Measures and into Power Query, again due to Direct Query limitations. I don't have a possibility to put the calculation outside of DAX, so I need to find a solution within measures, that I can use.

Where I'm at right now

My data looks a bit like this:

Item	Date	Primo	Sales	Purchase	Ultimo
1	16-08-2024	1.429	771	0	658
1	17-08-2024	658	1136	0	-1136
1	18-08-2024	0	4	384	380
1	19-08-2024	380	594	0	-594
1	20-08-2024	0	630	0	-630
1	21-08-2024	0	659	0	-659
1	22-08-2024	0	821	0	-821
1	23-08-2024	0	167	0	-167
1	24-08-2024	0	4	0	-4
1	25-08-2024	0	3	0	-3
1	26-08-2024	0	0	0	0
1	27-08-2024	0	0	0	0
1	28-08-2024	0	0	0	0
1	29-08-2024	0	0	0	0

I have tried to put everything into my ultimo calculation, but I can also see that that is the source of the error.

I have 3 measures that's just a sum of the data in the table:

WarehouseflowSales = SUM(warehouseflow[sales])
WarehouseflowPurchase = SUM(warehouseflow[purchase])
WarehouseflowInventory = SUM(warehouseflow[inventory])

Then I have the measure for the Ultimo column:

WarehouseflowUltimo =
VAR MaxDate = MAX(warehouseflow[date])

VAR UltimoYesterday =
    CALCULATE(
        -[WarehouseflowSales] + [WarehouseflowPurchase],
        ALLEXCEPT(warehouseflow, warehouseflow[itemno]),
        warehouseflow[date] < MaxDate
    )
    +
    [WarehouseflowInventory]

RETURN

    IF(
        UltimoYesterday < 0,
        0,
        UltimoYesterday + [WarehouseflowPurchase] - [WarehouseflowSales]
    )

The measure for the Primo column is just the ultimo value from the day before:

WarehouseflowPrimo =

VAR MaxDate = MAX(warehouseflow[date])

VAR UltimoYesterday =
    CALCULATE(
        [WarehouseflowUltimo],
        ALLEXCEPT(warehouseflow, warehouseflow[itemno]),
        warehouseflow[date] = MaxDate - 1
    )

RETURN

    IF(
        MaxDate = TODAY(),
        [LagerflowInventory],
        IF(
            UltimoYesterday < 0,
            0,
            UltimoYesterday
        )
    )

I have tried to edit the measure to use a somewhat circular reference, but whereas I handle the calculation for the WarehouseflowUltimo measure by date. Meaning that if the the date is today, then I should add the WarehouseflowPurchase and subtract the WarehouseflowSales to the WarehouseflowInventory. If it is not today, then I should use the WarehouseflowPrimo instead of the WarehouseflowInventory.

Do you have any suggestions as to how I can handle this?

Possible solution I can see myself

I have thought of a solution to create 14 different measures, since I need to handle 14 days within my forecast. Then I should be able to handle the issue day by day. I'm just a bit tied on hands and legs due to performance on this issue as well. I haven't tried it yet, but would like to hear your suggestions first 😊

[lbendlin · Accepted solution]

Spoiler alert: DAX does not support conditional accumulations.  You need to use Power Query's List.Accumulate for that.

[PatrickS-DK]

Thank you for your try @Rupak_bi.

I have tried to implement it, but in another scenario I get another error.

I have tried to create an Excel-spreadsheet and a PBIX file ontop of this Excel-spreadsheet, to have a couple of different scenarios that could happen 🙂

Right now it seems like if the previous day is greater than 0 and the next day should be as well, then it doesn't get the closing stock from the previous day.

The underlying dataset you can put into Power Query with this statement:

let
    Source = #table(type table[Item = text, Date = date, Inventory = number, Sales = number, Purchase = number], {{"1",45523,1429,771,0}, {"1",45524,0,1136,0}, {"1",45525,0,4,384}, {"1",45526,0,594,0}, {"1",45527,0,630,0}, {"1",45528,0,659,0}, {"1",45529,0,821,0}, {"1",45530,0,167,0}, {"1",45531,0,4,0}, {"1",45532,0,3,0}, {"1",45533,0,0,0}, {"1",45534,0,0,0}, {"1",45535,0,0,0}, {"1",45536,0,0,0}, {"2",45523,500,400,0}, {"2",45524,0,250,500}, {"2",45525,0,200,0}, {"2",45526,0,400,0}, {"2",45527,0,300,0}, {"2",45528,0,200,0}, {"2",45529,0,100,700}, {"2",45530,0,250,0}, {"2",45531,0,350,0}, {"2",45532,0,0,200}, {"2",45533,0,350,0}, {"2",45534,0,0,0}, {"2",45535,0,0,0}, {"2",45536,0,0,0}, {"3",45523,500,600,0}, {"3",45524,0,100,250}, {"3",45525,0,500,300}, {"3",45526,0,200,500}, {"3",45527,0,750,0}, {"3",45528,0,250,0}, {"3",45529,0,300,0}, {"3",45530,0,150,0}, {"3",45531,0,100,0}, {"3",45532,0,50,2500}, {"3",45533,0,650,0}, {"3",45534,0,750,0}, {"3",45535,0,300,0}, {"3",45536,0,0,0}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"

The Measure for Primo (based of the one you provided), is here:

Primo = 
var min_date = calculate(min('Data'[Date]),all(Data[Date]))
var prev = calculate(sum(Data[Purchase])-sum(Data[Sales]) + SUM(Data[Inventory]),all(Data[Date]),PREVIOUSDAY(Data[Date]))
var prev1= SUM(Data[Inventory]) + prev

RETURN

    max(0,prev1)

[lbendlin · Accepted solution]

Spoiler alert: DAX does not support conditional accumulations.  You need to use Power Query's List.Accumulate for that.

[PatrickS-DK · Accepted solution]

That's what I figured. List.Accumulate is not a possibility, due to Direct Query mode. I need the data to be as close to real time as possible, and we therefore went for Direct Query mode. It has a lot of limitations in the Power Query functionality and also in terms of calculated columns.

We'll try to see if we can do something manually to handle these steps instead. Although it would've been awesome to handle it in DAX.

[Rupak_bi]

Hi,

Here is My solution. Please let me know if this works.

I have created two measures as follows

Opening Stock =

var min_date = calculate(min('Table'[Date]),all('Table'[Date]))

var prev = calculate(sum('Table'[Purchase])-sum('Table'[Sales]),all('Table'),PREVIOUSDAY('Table'[Date]))

var prev1= if(max('Table'[Date])=min_date+1,prev+1429,prev)

RETURN

max(0,prev1)

Closing Stock =

Var min_date = calculate(min('Table'[Date]),all('Table'[Date]))

var closing = [Opening Stock]+sum('Table'[Purchase])-sum('Table'[Sales])

RETURN

if(max('Table'[Date])=min_date,1429+closing,closing)

[jack456jokky]

Hello,

I see your post 

Opening Stock Opening Stock =
VAR min_date = CALCULATE(MIN('Table'[Date]), ALL('Table'[Date]))
VAR prev = CALCULATE(SUM('Table'[Purchase]) - SUM('Table'[Sales]), ALL('Table'), PREVIOUSDAY('Table'[Date]))
VAR prev1 = IF(MAX('Table'[Date]) = min_date + 1, prev + 1429, prev)
RETURN MAX(0, prev1) 
Closing Stock

Closing Stock =
VAR min_date = CALCULATE(MIN('Table'[Date]), ALL('Table'[Date]))
VAR closing = [Opening Stock] + SUM('Table'[Purchase]) - SUM('Table'[Sales])
RETURN IF(MAX('Table'[Date]) = min_date, 1429 + closing, closing) These versions should be more streamlined while maintaining the same logic. VanillaGiftCardBalance