Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Calculate moving average only if data exists

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 prod...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

samratpbi's avatar
samratpbi
Super User
2 years ago

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!