Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi all
i'm really struggling to understand how to compile what should be a very simple MAA. The issue is that i do not use date, i use period numbers; 1 to 13, as below -
all i want is a moving average (for incidents) across the last 13 periods, so for P5 above the value would be the average of periods; 6,7,8,9,10,11,12,13,1,2,3,4,5.
i just cannot get it to work, any help much appreciated
Solved! Go to Solution.
Create a fiscal year period column in both tables the use it in the code. It is also advised to use this column to create the relationship.
Fiscal YearPeriod =
VALUE ( SUBSTITUTE ( 'SAFs Actuals'[Fiscal Year], "/", "" ) ) * 100 + 'SAFs Actuals'[Period]
thanks all - i'll be trying some of these tomrrow
Dan
Hi @dantheram
you can create a calculated column for fiscal year rank
Fiscal Period Rank =
RANKX (
'Date',
VALUE ( LEFT ( 'Date'[Fiscal Year], 4 ) ) * 100 + 'Date'[Period],
,
asc,
DENSE
)
then the measure would be
Incident Count MA =
VAR CurrentRank =
MAX ( 'Date'[Fiscal Period Rank] )
VAR T1 =
FILTER (
ALLSELECTED ( 'Date'[Fiscal Period Rank] ),
'Date'[Fiscal Period Rank] <= CurrentRank
&& 'Date'[Fiscal Period Rank] >= CurrentRank - 12
)
RETURN
AVERAGEX (
T1,
VAR CurrentRank2 = 'Date'[Fiscal Period Rank]
RETURN
CALCULATE (
[Incident Count],
REMOVEFILTERS ( 'Date' ),
'Date'[Fiscal Period Rank] = CurrentRank2
)
)
hi
this one fails at the rank stage -
"a single value for column 'financial year in table 'SAFs Actuals' cannot be determined. this can happen when a measure formula refers to...."
any ideas?
This is supposed to be a calculated column not a measure. Please read my answer above carefully
hi
apologies - i've added to the file as a calc'd column and it works but the same problem observed with the other solution offered here - no recalcualtion when i add in other categories; i.e. if i split the incidents counts down by geography the MAA stays based on the total
thanks
Dan
@dantheram
Also please note that a Date table is required for this solution to work properly.
so is the rank added to the 'date' table or the 'incident count' data table?
done - but its simply returing the period value >>>>
correct - i had used the wrong date table
just need to tidy up the output now; how do i stop it starting at 600 and running on past period 5 (the last period with data, so the year to date in effect)?
hi Tamer
the main issue is the calc running on past the year to date row - period 5, so it's picking up all the 0's and reducing the MA value, i need it to stop at the max value for period in the incident count dataset - so period 5
@dantheram
Please try
Incident Count MA =
IF (
NOT ISEMPTY ( 'Table' ),
VAR CurrentRank =
MAX ( 'Date'[Fiscal Period Rank] )
VAR T1 =
FILTER (
ALLSELECTED ( 'Date'[Fiscal Period Rank] ),
'Date'[Fiscal Period Rank] <= CurrentRank
&& 'Date'[Fiscal Period Rank] >= CurrentRank - 12
)
RETURN
AVERAGEX (
T1,
VAR CurrentRank2 = 'Date'[Fiscal Period Rank]
RETURN
CALCULATE (
[Incident Count],
REMOVEFILTERS ( 'Date' ),
'Date'[Fiscal Period Rank] = CurrentRank2
)
)
)
this is so nearly there, 1 issue, when there are 0 incidents in a period this happens
so we need the calc to process the '0' rows as '0' unless greater than period 5 - where we have no data as its in the future
the above is ignoring legitmate '0s' as blanks
@dantheram
Please try
Incident Count MA =
VAR LastDateWithData =
CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR CurrentDate =
MAX ( 'Date'[Date] )
RETURN
IF (
CurrentDate <= LastDateWithData,
VAR CurrentRank =
MAX ( 'Date'[Fiscal Period Rank] )
VAR T1 =
FILTER (
ALLSELECTED ( 'Date'[Fiscal Period Rank] ),
'Date'[Fiscal Period Rank] <= CurrentRank
&& 'Date'[Fiscal Period Rank] >= CurrentRank - 12
)
RETURN
AVERAGEX (
T1,
VAR CurrentRank2 = 'Date'[Fiscal Period Rank]
RETURN
CALCULATE (
[Incident Count],
REMOVEFILTERS ( 'Date' ),
'Date'[Fiscal Period Rank] = CurrentRank2
)
)
)
the issue with the above is that the 'lastdatewithdata' and max current date lines will always be '13', as i have to use the period numbers and they reoccur each year - so there's always a 13.
so, it is working but still running on past period 5
Create a fiscal year period column in both tables the use it in the code. It is also advised to use this column to create the relationship.
Fiscal YearPeriod =
VALUE ( SUBSTITUTE ( 'SAFs Actuals'[Fiscal Year], "/", "" ) ) * 100 + 'SAFs Actuals'[Period]
you sir are a hero 🙂
thanks so much!
No. Please look carefully at the code. The last date is calculated from the fact table not the date table. It should work
User | Count |
---|---|
57 | |
21 | |
19 | |
17 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |