Forum Discussion

JXHK_22's avatar
JXHK_22
Copper Contributor
Jan 29, 2022

Help with moving average of a DAX measure

Grateful for any help here. I have set up the following measure (Distinct Transaction) to calculate the number of distinct transactions under certain categories based on the table structure shown. 

 

I've been trying to make another measure to calculate a 12-month moving average of this measure, but keep running into errors (I'm a relative newbie to DAX, following Youtube tutorials - but they do not seem to work against an existing measure).  I'm aiming to chart this 12 month moving average as a line chart on a dashboard. 

 

Suggestions much appreciated. 

 

Distinct Transaction = CALCULATE(DISTINCTCOUNT (ServiceDetail[Activity ID]), ServiceDetail[Transaction Type] = "New Sale" || ServiceDetail[Transaction Type] = "Repeat Sale" || ServiceDetail[Transaction Type] = "Site Repair" )

 

BI & Data Analysis
excel
Formulas and Functions
power bi
    • JXHK_22's avatar
      JXHK_22
      Copper Contributor
      Jan 31, 2022
      Thanks for your help Sergei. I've tried to adapt the sample DAX from the site to my scenario (as below) . For some reason, I end up with constant values of 1.0 as a result. Any idea what I've done wrong? I've not used some of these measures previously. Thank you.

      365 Day Moving Average =
      Var NumDays = 365
      Var LastCurrentDate = MAX(ServiceDetail[Service Date])
      Var Period = DATESINPERIOD (ServiceDetail[Service Date]), LastCurrentDate, -NumDays, DAY)
      Var Result =
      CALCULATE(AVERAGEX(ServiceDetail, [Distinct Transaction]), Period)
      VAR FirstDateInPeriod = MINX(Period, ServiceDetail[Service Date])
      Var LastDateInPeriod = MAX(ServiceDetail[Service Date])
      RETURN
      IF (FirstDateInPeriod <= LastDateInPeriod, Result)




      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jan 31, 2022

        JXHK_22 

        As a minimum, to use Time Intelligence function in DAX, like DATESINPERIOD(), you need to have table marked as Date Table. You may connect one from corporate server if you have such table on it (most preferrable way), or create by Power Query, or create Calendar table directly in Power Pivot (less preferable due to maintenance).

        In any case be sure it's marked as Date Table.

        Link ServiceDetail table with calendar on Date, hide [Service Date] field and use only Calendar dates in measures.

        Perhaps something else, but that's better to discuss with sample file.

         

Resources