Average of cumulative total (ie monthly average of daily inventory balances)

[Anonymous]

Hi,

I have a measure that gives me daily inventory balances which works well

Balance Qty = ROUNDDOWN(CALCULATE(SUM('Item Ledger'[Quantity]),FILTER(all(Dates),Dates[Date]<=max(Dates[Date]))),0)

Now I want to take that and get monthly (and probably quarterly and yearly) averages of the daily average.  But I can't figure out how to take an average of another measure.

The average functions all want to be on a column.  I tried creating a groupby table from my main movement table, but I can't figure out how to make that cumaltive.

Thanks,

[Ashish_Mathur]

Hi,

Theoretically, the AVERAGEX() function should work well.  To get more specific help, share some data and show the expected result.

[Anonymous]

I also tried

Balance Qty Daily = CALCULATE(SUM('Item Ledger Daily'[Quantity]),ALL('Item Ledger Daily'),'Item Ledger Daily'[Item Ledger_Posting Date]<=EARLIER('Item Ledger Daily'[Item Ledger_Posting Date]))

based on this post here  https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115

which does give me a cumulative total, but it doesn't take into account the item row context

[Anonymous]

I'm not sure how you use averageX here.  It only works on columns correct?  But what I have is a measure (that has the daily balance).  But I'm not sure how to get the daily balance (and cumalative total all all the inventory transactions) into a column.

I tried a calculated column such as

Balance Qty Daily = CALCULATE(SUM('Item Ledger Daily'[Quantity]),FILTER(ALL(Dates[Date]),Dates[Date]<=MAX(Dates[Date])))

But that doesn't seem to work.  It doesn't get a cumalative total, just the daily total.

I also tried doing sumx to get the balance, same problem

Balance Qty Daily = CALCULATE(SUMX('Item Ledger Daily','Item Ledger Daily'[Quantity]),FILTER(ALL('Item Ledger Daily'[Item Ledger_Posting Date]),'Item Ledger Daily'[Item Ledger_Posting Date]<=MAX('Item Ledger Daily'[Item Ledger_Posting Date])))

[Ashish_Mathur]

Hi,

As requested in my previous message, share the link from where i can download your PBI file and show the expected result there.

[Anonymous · Accepted solution]

I figured it out.  I just needed to put a second filter on it to take into account the item numbers as well as the date

Balance Qty Daily = CALCULATE(SUM('Item Ledger Daily'[Quantity]),ALL('Item Ledger Daily'),'Item Ledger Daily'[Item Ledger_Posting Date]<=EARLIER('Item Ledger Daily'[Item Ledger_Posting Date]),'Item Ledger Daily'[Item Ledger_Item No_] = EARLIER('Item Ledger Daily'[Item Ledger_Item No_]))