Forum Discussion
Calculate moving average only if data exists
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.
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.
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 ?
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
