Rolling Average not working if no margin in latest month

[gbereczk]

Hi all,

I'm trying to calculate 12M rolling average margin. This works fine, however when there is no margin (no revenue or cost) in the latest month, the formula ignores the month and just shows the rolling average for last month instead of adding zero margin and dividing it by +1 month. Probably this makes more sense looking at this picture:

As you can see, in Jun-21 there's no margin or average margin and the rolling average amrgin shows 607.19 rather than 539.72.

The DAX I used:

Average margin = AVERAGEX(VALUES('Calendar'[Date]),[Margin])

12M rolling average margin = calculate([Average margin],DATESINPERIOD('Calendar'[Date],max('Calendar'[Date]),-12,MONTH))

Thanks in advance!

[v-shex-msft]

HI @gbereczk,

In fact, it is hard to test from your snapshot. Can you please share a pbix file with some dummy data that keep the raw table data structure and expected results? They will help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,
Xiaoxin Sheng

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[gbereczk]

Sorry Im not able to share the full file. 

[Mohammad_Refaei]

Most likely the issue here is from the [Margin] formula. Please share it.

[gbereczk]

The Margin is just Revenue - Costs. The reason why Revenue is the same as Margin in the abpve example is because there are no costs for this specific customer.