Jan 29 2022 05:05 AM
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" )
Jan 29 2022 06:27 AM
Jan 31 2022 04:14 AM
Jan 31 2022 04:31 AM
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.
Feb 02 2022 04:10 AM
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 =
Feb 02 2022 01:52 PM
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
)