Cumulative sales previous year in Stock running

[Talal141218]

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 

[v-pgoloju]

Hi @Talal141218,

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

[v-pgoloju]

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

[Praful_Potphode]

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

[Talal141218]

@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 🙂

[Praful_Potphode · Accepted solution]

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

[Talal141218]

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

[parry2k]

@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