Forum Discussion

Millar's avatar
Millar
Frequent Visitor
2 years ago

Dax query

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))

 

Need Help
Tips and Tricks

2 Replies

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Community Support
    2 years ago

    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.

  • Millar's avatar
    Millar
    Frequent Visitor
    2 years ago

    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)