I am interested in replacing an iterative xls solution and I would be grateful for some advice please.
Requirement is to:
To calculate the average 'initial score' - this is the average "earliest score" for Players that have more than 1 score in a given date range, the date range would be selected prior to the calculation. In the example below for the date range of 1/01/2018 - 1/06/2018 we would exclude records for Players E & F because they each have only 1 score for this date range.
To calculate the average 'proceeding score' - this is the average 'non-earliest score' for Players that have a count of more than 1 score for a given date range, the date range would be selected prior to the calculation. In the example for the date range of 1/01/2018 - we would exclude E & F because they each have only 1 score.
I am wondering what the best approach would be to address this.
Would something like the following be a suitable approach?
Create a calculation for -
1/ a total count of records per customer;
2/ the earliest date per player;
3/ a matching attribute i.e. if "2/ earliest date per player" is equal to the Date then return 'earliest' else 'not earliest'.
and then somehow -
5/ Exclude / filter out players with a "1/ total count of records" equal to only 1 from proceeding calculations;
6/ Calculate the average earliest score where "4/ matching attribute" equal to 'earliest';
7/ Calculate the average proceeding score where "4/ matching attribute" equal to 'not earliest'.
I have another question regarding this solution that you kindly presented a little while ago.
I had tested this further with a different range of dates i.e. where there is a different minimum date per player. I have found that the use of FIRSTDATE with GENERATE seems to restrict or filter the result to where a player has a minimum date that is equal to the minimum from the whole result set, rather than the minimum per player ( or from the window).
From what I can tell, the measures should be behaving correctly even when players have different minimum dates, taking into account any date filters applied as well.
Could you post some sample data or a PBIX containing unexpected results, ideally with a visual showing the context where you are using the measures? There could be something I didn't consider when the measures are used in a certain context.
In particular FIRSTDATE ( Scores[DATE] ) is internally converted to FIRSTDATE ( CALCULATETABLE ( VALUES ( Scores[DATE] ) ), so context transition should give the first date per Player being iterated over inside GENERATE.
Thank you again much appreciated and thank you for the link as well which I have been looking over. I did encoutner an error when using the variable based calcs, with a different data set. The error was - Mdx script model (9,38), a date column containing duplicate dates was specifiedin a call to function FIRSTDATE.
I did attempt another longer winded approach with more calcs - this is perhaps not best practice but this ended up providing desired results:
Is it possible to create something similar but as a measure that would be responsive to the filtering of a column like Sheet3[Date] that would be controlled by a user from a slicer prior to the calculation being evaulated? The purpose would be to display the min and max dates per client (client being the window or subset) but for a selected date range rather than the full dataset.