Moving 3 month average percentage of total

[Anonymous]

Hi,

I'm trying to calculate the moving 3 month avearge percentage.

Below is the table screenshort and the dax calculations I used.

TotalValue = CALCULATE(Sum(Tbl1[Balance])+0)

TotalValue_Worse = CALCULATE(Sum(Tbl[Balance]), filter(Tbl1, Tbl1[position] = "Worse"))

Worse_% £ =

VAR _Volume =   CALCULATE(Sum(Tbl[Balance]), filter(Tbl1, Tbl1[position] = "Worse"))

VAR _AllVolumes = CALCULATE(Sum(Tbl1[Balance])+0)

//Calculating the ratio

VAR _VolumePct =

    DIVIDE(_Volume,_AllVolumes)

RETURN

_VolumePct

How do I add additional dax to calculkate the moving 3 month %Worse average, so for November it will be 0.45%,(0.48+0.35+0.52)/3 , October 0.44% ,(0.49+0.48+0.35)/3, and so on.

The Dateid column is in the same table and only has month end dates and formatted to date/time.

Thank you

[Anonymous]

Thank you.

It worked for values. But when I tried to create the same view for volumes, the output is the same for all the dates.

Total %Worse R3M,Vol =

VAR NumOfMonths = 3

VAR LastCurrentDate =

    MAX (Query2[DateID] )

VAR Period =

    DATESINPERIOD ( Query2[DateID], LastCurrentDate, - NumOfMonths, MONTH )

 VAR Result =

    CALCULATE (

        AVERAGEX(

            VALUES ( Query2[DateID]),

            [Worse_%2]

        ),

        Period

    )

Return Result

Do I have to add/remove filter for it to work?

[v-xiaosun-msft]

Hi @Anonymous ,

You can use "DATESINPERIOD" function to return the rolling 3 months.

filterDate =  DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )

Then use CALCULATE function and AVERAGEx function and you can reference the following document.

Rolling 12 Months Average in DAX - SQLBI

Best Regards,
Community Support Team _ xiaosun

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

[Anonymous]

Hi,

Do you know why my rolling avearge values are the same?