Calculate moving average only if data exists

[Syndicate_Admin]

Hello everyone!, I have the following question:

I'm working on a sales dashboard and I need to calculate the average sale for the last 3 months.

I have as information the monthly sale for each product which is updated at the end of each month.

I currently have this syntax:

Vta M-3 YES =

var fecha_max=

CALCULATE(

MAX('VTAS SI'[Date]),

ALL('VTAS SI'[Sell In]))

Var fecha_min=

EOMONTH(fecha_max, -3)+1

Var Result =

CALCULATE([Vta SI],FILTER(CALENDAR, CALENDAR[Date]>=fecha_min && CALENDAR[Date]<=fecha_max))

Return Result

The drawback is that I have products that report sales until December 2023 (it was later discontinued).

Therefore, the above syntax considers me the average sale of December 2023, November 2023 and October 2023 since they are the last 3 months that there is sales information.

As of January 2024 there is no data.

What I want to correct is that if there is no sales data in the last 3 months, the average is 0.

In this case, those products do not have sales information in June 2024, May 2024 and April 2024.

I appreciate your support!

Thank you!

[v-zhouwen-msft · Accepted solution]

Hi @samratpbi ,Thanks for your quick reply, I will add more.

Hi @Crisvilla88 ,

Regarding your question, you are determining the max and min dates from your fact table, so there will be no data starting from January 2024.(There is no data from January 2024, and the value returned by 'fecha_max' is blank)

You should get the maximum and minimum dates from the date table.

Measure 2 = 
VAR _maxDate = MAX('Calendar'[Date])
VAR _minDate = EOMONTH(_maxDate,-3) + 1
VAR _maxDateForSales = CALCULATE(MAX('Table'[Date]),ALL('Calendar'))
VAR _result = 
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Calendar'),[Date] >= _minDate && [Date] <= _maxDate))

RETURN 
IF(MIN('Calendar'[Date]) >= _maxDateForSales && ISBLANK(_result),0,_result)

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

[Syndicate_Admin]

Dear,

On other tables I have future months forecast in units, so the calendar table that I have on this file its until december 2025.

Could your proposal works even in this case?

• I have a database of sales until the end of June.
• Products that has sale information until December 2023 (so I'm looking for the sales -average- of October 2023, November 2023 and December 2023.)
• The calendar table that I have it's until December 2025.

If I set the max and min Date from the Calendar table, could your proposal give me the sales average of October 2025, November 2025 and December 2025 (That n this case would be 0 due that the sales database its until June 2024 ?

[v-zhouwen-msft]

Hi @Crisvilla88 ,

Regarding your question, since there will be no sales data after December 2023, the latest value will be February 2024, and all subsequent months will be displayed as 0.(2024-3 = 0 , 2024-4 =0 ,2024-5 = 0......2025-12 = 0)

If the above expression does not meet your needs, please provide simple data and display the expected results in the form of pictures or share a .pbix file without sensitive data.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Wenbin Zhou

[samratpbi]

Hi,

It seems the problem is with how you are identifying the Max Month. Not sure about which column contains what data, however, you need to identify Max month of the overall. Hence it seems instead of using filter ALL('VTAS SI'[Sell In]) while fetching max date, you may use only ALL('VTAS SI'), which will remove filter context of anything on that table. Then your max month should be be June 2024.

Hope this helps to resolve your problem. If it does, then please mark it as solution, Thanks!