Forum Discussion

Saaam's avatar
Saaam
Frequent Visitor
2 years ago
Solved

Stock by month from transactions with missing data

Hi,

i'm trying to calculate the inventory stock by month pulling data from transactions.

The issue is that if for a specific month i may have no transactions , so i'm showing no stock instead the last previous stock.

 

My model is:

- A calendar table (last 12 months)

- B transactions (with some GAP if there are no transactions for some period)

 

Currently i did a running qty total on B then a summarize table to group for every item,end of month the stock qty.

So this is table C

 

A (calendar) join C (summarized transactions) 1-*

 

As you can see 31/08/2023 is missing because i have no transactions.

 

Currently i'm showing blank but i need to show the last non blank value, 3.

Stock is not missing, simply there was no inventory transactions so it doesn't change.

 

How can i achieve that?

I need to fill blank value with the last non blank value (could be previous month, 3 month ago, 1 year....)

 

I've tried a lot of solutions on this forum but no one works for this scenario.

 

Thanks in advance

 

Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    Hi,

    Try this:

    MonthStock = 
VAR _Stock = SUM(TabStock[StockQty])
VAR _MaxStockDate = CALCULATE(MAX(TabStock[EndOfMonth]),ALL(TabStock))
VAR _LastDateWStk = 
    CALCULATE(
        MAX(d_Calendar[Date]),
        FILTER(
            ALL(TabStock),
            TabStock[EndOfMonth]<=MAX(d_Calendar[Date]) && MAX(d_Calendar[Date]) <= _MaxStockDate
        )
    )

VAR _StockLastDate = 
    CALCULATE(
        TabStock[Stock],
        d_Calendar[Date]=_LastDateWStk
    )

VAR _Result = 
    IF(
    _Stock<>0,
    _Stock,
    _StockLastDate
)
RETURN
_Result

     

     

3 Replies

  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi,

    Try this:

    MonthStock = 
VAR _Stock = SUM(TabStock[StockQty])
VAR _MaxStockDate = CALCULATE(MAX(TabStock[EndOfMonth]),ALL(TabStock))
VAR _LastDateWStk = 
    CALCULATE(
        MAX(d_Calendar[Date]),
        FILTER(
            ALL(TabStock),
            TabStock[EndOfMonth]<=MAX(d_Calendar[Date]) && MAX(d_Calendar[Date]) <= _MaxStockDate
        )
    )

VAR _StockLastDate = 
    CALCULATE(
        TabStock[Stock],
        d_Calendar[Date]=_LastDateWStk
    )

VAR _Result = 
    IF(
    _Stock<>0,
    _Stock,
    _StockLastDate
)
RETURN
_Result

     

     

    • Saaam's avatar
      Saaam
      Frequent Visitor
      2 years ago

      Hi,

      if i write the measure in my model it doesn't work

       

       

      This is my summarize

      If i copy and paste this output table in a new .pbix and try your measure it works

       

      I'm trying to figure it out what i am doing wrong!

       

       

       

      • _AAndrade's avatar
        _AAndrade
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        Right, I did an example with the data that you shared. You need to understand how did you build your model in order to write the right measure.