topic Re: Cumulative sales previous year in Stock running in DAX Commands and Tips

Cumulative sales previous year in Stock running

Talal141218 — Mon, 03 Nov 2025 11:37:21 GMT

Hi Pofis,

I am still struggling to calculate the running stock - I've almost achived ir , but i am still having trouble calculating the cumulative sales for the pervious year, please find my formula and a screen shot to clarify the issue :

Purchases up to date = CALCULATE(Fact_PurchOrderLine[CM_PurchOrderline_Qty_piec], Fact_PurchOrderLine[PurchOrderlineDateEstimatedArrival] <= MAX(Dim_DateDue[DateDue_NK]) && Fact_PurchOrderLine[PurchOrderlineDateEstimatedArrival] >= TODAY()).

Sales up to date = CALCULATE([CM_SalesOrderLine_CountOpen], Fact_SalesOrderLine[ShippingDateRequested_NK] <= MAX(Dim_DateDue[DateDue_NK]) && Fact_SalesOrderLine[ShippingDateRequested_NK] >= TODAY())

Absatz VJ =

VAR LastYear = TREATAS(SAMEPERIODLASTYEAR(VALUES(Dim_DateDue[DateDue_NK])), Dim_SalesOrderPackageDate[Date_NK])

VAR SalesLY = CALCULATE(Fact_SalesOrderPackage[CM_Fact_SalesOrderPackage_ItemQuantity],LastYear)

RETURN IF(SalesLY = BLANK(), BLANK(), CALCULATE(SalesLY, FILTER(ALLSELECTED(Dim_DateDue[DateDue_NK]), Dim_DateDue[DateDue_NK] <= MAX(Dim_DateDue[DateDue_NK]))))

Running Stock =

VAR Stock = [Lager Bestand exlu.Rev.]

VAR Incoming = Fact_PurchOrderLine[Purchases up to date]

VAR sales = Fact_PurchOrderLine[Sales up to date]

VAR Absatzplan = [Absatz VJ]

RETURN

Stock + Incoming - sales - Absatzplan

Thanks in Advance

Re: Cumulative sales previous year in Stock running

parry2k — Mon, 03 Nov 2025 16:43:40 GMT

Talal141218 I did a video on something similar a long time ago, hope it gives you some clue

On what date product will run out of stock - a use case of CROSSJOIN in Power BI

Re: Cumulative sales previous year in Stock running

Talal141218 — Tue, 04 Nov 2025 08:27:54 GMT

parry2k Thanks for your reply, but unfortunatly the video did't help me in this issue.

Re: Cumulative sales previous year in Stock running

Praful_Potphode — Wed, 05 Nov 2025 11:56:03 GMT

Hi Talal141218

based on the snapshot , i could not undertand the desired output .can you clarify that to me?

Also on the intial analysis , it looks like there is an issue in Absatz VJ Measure.can your break the measure into two parts i.e. one base measure and one time intelligence measure.try creating below 3 measures and let me know.

Base Sales Count = SUM(Fact_SalesOrderPackage[CM_Fact_SalesOrderPackage_ItemQuantity])

Absatz VJ (Cumulative LY) = VAR MaxDate = MAX(Dim_DateDue[DateDue_NK]) VAR DatesLY = // 1. Get the range of dates from the start of the year // UP TO the date one year PRIOR to the current MaxDate. DATESBETWEEN( Dim_DateDue[DateDue_NK], STARTOFYEAR(SAMEPERIODLASTYEAR(Dim_DateDue[DateDue_NK])), SAMEPERIODLASTYEAR(MaxDate) ) RETURN // 2. Calculate the total sales within that specific date range (DatesLY) CALCULATE( [Base Sales Count], // Use your simple sales count measure here DatesLY, REMOVEFILTERS(Dim_DateDue) // Clear all existing date filters to apply the custom range )

Running Stock = VAR Stock = [Lager Bestand exlu.Rev.] VAR Incoming = [Purchases up to date] // Needs to be a measure, not a column reference VAR Sales = [Sales up to date] // Needs to be a measure, not a column reference VAR CumulativeSalesLY = [Absatz VJ (Cumulative LY)] // The FIXED cumulative measure RETURN Stock + Incoming - Sales - CumulativeSalesLY

try these measures and if it doesn't work please explain the desired outcome and how its supposed to filter.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Re: Cumulative sales previous year in Stock running

Talal141218 — Thu, 06 Nov 2025 10:49:36 GMT

parry2k I realy appreciate your response, but unfortunately your measure didn't work " I can't use Maxdate as a date in Function sameperiodlastyear. I want to ask you now another soultion. Forthermore: I want for make the ending stock of week , quarter, month as openning stock for next week, quarter etc.. and dedect it from the sales or purchase in the same period, week, quarter .. As you see in the screeen shot below :

Can you help in this issue ? Thanks in Advance 🙂

Re: Cumulative sales previous year in Stock running

Praful_Potphode — Fri, 07 Nov 2025 18:19:03 GMT

Hi Talal141218 ,

Explore Visual calcualtions they can help you to get values from the same visual without any complex logic.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview

Please give kudos or mark it as solution once confirmed.

Thanks and regards,

Praful

Re: Cumulative sales previous year in Stock running

Anonymous — Mon, 10 Nov 2025 03:42:15 GMT

Hi Talal141218,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to Praful_Potphode and parry2k for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

Re: Cumulative sales previous year in Stock running

Anonymous — Fri, 14 Nov 2025 04:43:38 GMT

Hi Talal141218,

Best regards,
Prasanna Kumar