Dax query

[Millar]

can any one suggest me the correct dax code for 12 month rolling average ( i try to do it but its showing incorrect result)

My code :-
12 Month Rolling Average = CALCULATE(AVERAGEX('DMADP OPEM_ADP_DATE_D',[Total TRs]), DATESINPERIOD('DMADP OPEM_ADP_DATE_D'[Date], MAX('DMADP OPEM_ADP_DATE_D'[Date]), -12, MONTH))

Just for your info
meaning of Year-Month (date type date) - Column which is newly created in PBI ( like add column)
meaning of Total TRs( data type text) - Dax query ( count(TRid))

 

[Millar]

Hi yulina, thanks for your response... FYI [Total TR] is not coming from date dimention table. Its a another dax measure (Total TR= count(fact table TRID)). Also note TRid is in the form of text ( which has values like alpha numerical (Ex ASDJ-2765)

[v-weiyan1-msft]

Hi @Millar ,

Based on your description,

You might consider using the following code to create a Measure.

12 Month Rolling Average = 
VAR _LastDate= MAX('DMADP OPEM_ADP_DATE_D'[Date])
RETURN
AVERAGEX(
    FILTER(
        SUMMARIZE(ALL('DMADP OPEM_ADP_DATE_D'),
                  'DMADP OPEM_ADP_DATE_D'[Date],
                  "Avg value",AVERAGE('DMADP OPEM_ADP_DATE_D'[Total TRs])
        ),
        'DMADP OPEM_ADP_DATE_D'[Date]<= _LastDate
        ),
        [Avg value]
)

Result is as below.

Is this the result you expect?
Please correct me if I misunderstood your needs.

Best Regards,
Yulia Yan

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