Forum Discussion

vpsoini's avatar
vpsoini
Helper I
4 years ago
Solved

Running total from multiple sources , starting from ground value?

Hello, and happy 2022!    My goal is to get inventory forecast, so I'm trying to calculate running total from multiple sources (sales and purchases) , but not really nailing it...  sales and purcha...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, vpsoini 

    ItemNo 2 should have a remaining amount of 19 on January 12.

     

    Try to create a measure like this:

    Measure = 
SUM(Items[Inventory])+
CALCULATE(
    SUM(ItemLedger[Qty]),
    FILTER(ALL(ItemLedger),'ItemLedger'[ItemNo]=MAX('Items'[ItemNo])&&'ItemLedger'[Date]<=MAX('ItemLedger'[Date])))

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
4 years ago

vpsoini , This should use a calendar table

 

like

CALCULATE(SUM('Purchases'[Quantity]),FILTER(ALLSELECTED('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))
CALCULATE(SUM('Sales'[Quantity]),FILTER(ALLSELECTED('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))

 

and month, date, qtr, year table should always come from Calendar

 

Also, you might need all not allselected

 

CALCULATE(SUM('Purchases'[Quantity]),FILTER(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))
CALCULATE(SUM('Sales'[Quantity]),FILTER(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))

 

 

or like


Inventory =
Var _max = maxx(allselected('Calendar') , 'Calendar'[date])
return
CALCULATE(SUM('Purchases'[Quantity]),FILTER(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] <=_max )) -
CALCULATE(SUM('Sales'[Quantity]),FILTER(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] <=_max ))