Help with moving average of a DAX measure

Copper Contributor

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

 

45278E5E22D84C00BCEA4ABB009BE4DF (1).jpeg

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




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

image.png

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.

 

@Sergei Baklan 

 

Thanks Sergei. I do have [Service Date] column marked as a date field. 

I figured that I mistakenly copied the AVERAGEX measure from the example...in my source data, each row represents a record of service interaction, so actually I need to count the rows. I've now set up the moving average with a simpler DAX formula as below. 

 

But I'm wondering if there is an alternative measure to DATESINPERIOD that would apply to the 30 preceding dates with observations instead of the 30 preceding calendar days?

 

Mov Avg = 

VAR NumDays = 30
VAR RollingCount = CALCULATE(COUNT(‘ServiceDetail’[Activity ID]), DATESINPERIOD(‘ServiceDetail’[Service Date], LASTDATE(‘ServiceDetail’[Service Date]), -NumDays, DAY))
RETURN
RollingCount/NumDays
 
Many thanks again. 

@JXHK_22 

If days are not sequential (e.g. you have only dates with observations) the pattern could be

periodStartDate :=
VAR backDays = 30
VAR cDate =
    TODAY ()
RETURN
    CALCULATE (
        TOPN (
            1,
            TOPN ( backDays, VALUES ( Table1[Date] ), Table1[Date], DESC ),
            Table1[Date], ASC
        ),
        Table1[Date] <= cDate
    )