Average of running closing balance [depending on slicer]

[rpfc]

Hi,

New to PowerBI - 2 months of reading up on and getting familiar with it. Got the basics (I hope), but the Dax Im struggling with big time. Been weeks trying to find a solution on these boards and only partially there .. Apologies if a solution is out there, but I couldnt find it!

Please refer attached file. I have got sales and expiries of goods over the course of a few years. Expiry happens 365 days after purchase. In essence, I am trying to calculate a running balance of goods that remain valid at any given point (day, week, month) in time (i.e. sales less expiries).

Linked is the PBI file where i recreated the problem (due to sensitive nature of data). 

https://drive.google.com/file/d/1Wk6i9zjkjKbMpPnUDjZPowNPwJ2aOm1a/view?usp=sharing 

I have somewhat succesfully created a daily running tally using :

Sales: RBSales = calculate(sum(Sales[Value]),filter(all('DateCalendar'[Date]),'DateCalendar'[Date]<=max(Sales[Date])),'Sales'[Type]="Product A")

Expiries: RBExpiry = calculate(sum(Expiry[Value]),filter(all('DateCalendar'[Date]),'DateCalendar'[Date]<=max(Expiry[Date])),'Sales'[Type]="product a")

I have 2 issues:

1. The issue is that when I show my rows in weeks (or months, years for that matter), I need to get an average of the sales, an average of the expiries - and therefore an average of the balance for the corresponding weeks. I have created 2 tables in the PBI file illustrating the fact that right now, it shows a value that doesnt quite match what I need. I have used Averagex to try and come up with the values for the week - but no luck. Example: week 1 average sales volume should be about 9,593, expiries about 5,783, so average week 1 balance should be 3,810. My report said 3,793 - small difference, but Im sure my logic is off somewhere or my understanding not quite right...

Note, if I were to use the modeling parameter (to switch between days, weeks, months, years - I would like the averages to be automatically recalculated to reflect correct numbers for the relevant time periods).

2. Issue number 2 is that in the real file I have , I have a lot more data - 100k+ rows, with dozens of different sources. I do use the star scheme and have tried to flatten most sources as much as I could. I am sure Power BI should be able to handle this, but using AverageX or SumX is resulting in very slow results (1 minute+ loading / refreshing times). It does contain a lot of other info - which I cant share - so as a general question, can I get the correct rolling average for issue #1 by not using Sumx or Averagex (or other Iterating functions) which seems to cause the slowness? 

Many thanks in advance,
AR

[Anonymous]

Hi @rpfc ,

I don't have aceess to your sample file. Please share a sample file with me again and show a screenshot with the result you want. This will make it easier for me to find the solution.

Are [Week] and [Date] from same table?  I suggest you to create a DimDate table by dax and relate it with Fact Data table by [Date] columns. [Week] column should be a up level than [Date]. Then you can create a matrix with hierarchy level in Row.

If you want to create average in different levels, you can try ISINSCOPE() function.

Power BI Performance is based on your data model, size of your data, complexity of code and so on. You may refer to below offical blog to learn more details.

For reference:

Optimization guide for Power BI

Best Regards,
Rico Zhou

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