Average Calculated on each instance

[TIGER8855]

I have a report where I have created a column in my source table that shows the amount of days between two dates. If either or both dates are empty, then there is no value in my created column.

I have then created a visual that shows an entry every time there is a value in my created column. I now want to add a rolling average to my visual that will change every time there is an instance of that value. The dates however are quite random so it can't be an average based on a set period, rather it needs to be based on each instance. Below is an example of my source table.

ID	Date1	Date2	Days
1	01/05/2018	09/08/2018	100
2	01/11/2018
3	12/03/2019	01/05/2019	50
4	18/09/2019
5	22/12/2019
6	02/04/2020	16/10/2020	197
7	05/08/2020

So in my visual, I have the ID as the Horizontal Axis, and the Days as the vertical axis represented by columns. The horizontal axis is sorted by Date2. I want to add a line value to the visual that calculates the average of the days based on instances. So based on my example table, the first column in my visual would be ID 1 with 100 Days Value and then the next entry would be ID 3 with 50 Days value.

The average would start at 100 and then upon the second entry, the avaerage would change to 75 (100 + 50 / 2 = 75). This average would change based on every instance of Days not by a rolling average of a set period.

[v-luwang-msft]

HI @TIGER8855 ,

Ok,I know.Pls test the below:

step1,create a new column:

rankdate2 = IF('Table'[Date2]= BLANK(),BLANK(), RANKX(FILTER('Table','Table'[Date2]<>BLANK()),'Table'[Date2],,ASC,Dense))+0.001*'Table'[ID]

Then change the  another dax to the below:

average = 
VAR sum1 =
    CALCULATE (
        SUM ( 'Table'[Days] ),
        FILTER ( 'Table', 'Table'[rankdate2] <= EARLIER ( 'Table'[rankdate2] ) )
    )
VAR count1 =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER (
            'Table',
            'Table'[rankdate2] <= EARLIER ( 'Table'[rankdate2] )
                && 'Table'[Days] <> BLANK ()
        )
    )
RETURN
    IF ( 'Table'[Days] = BLANK (), BLANK (), sum1 / count1 )

Output result:(I add a new value  that a id with the same date2 )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Lucien

[TIGER8855]

Thank you @v-luwang-msft , this new solution addresses my additional issue regarding the grouping of entries with the same date.

As this issue could be particular to my situation, I will accept your first answer as the solution as it addresses the initial qeury regarding the calculation of average on each instance.

Thank you so much for your help!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-luwang-msft · Accepted solution]

Hi @TIGER8855 ,

Test the below to create a new column:

average = 
VAR sum1 =
    CALCULATE (
        SUM ( 'Table'[Days] ),
        FILTER ( 'Table', 'Table'[ID] <= EARLIER ( 'Table'[ID] ) )
    )
VAR count1 =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER (
            'Table',
            'Table'[ID] <= EARLIER ( 'Table'[ID] )
                && 'Table'[Days] <> BLANK ()
        )
    )
RETURN
    IF ( 'Table'[Days] = BLANK (), BLANK (), sum1 / count1 )

Output Result:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Lucien

[TIGER8855]

Thank you @v-luwang-msft for replying!

Your solution is what I was after. The only issue is that my example table did not completely reflect my data set where Date2 order is not in the same order as ID (i.e. Date2 for ID number 1 can be later than the Date2 for ID number 3).

For this reason, I changed the FILTER in your Solution from 'Table'[ID] to 'Table'[Date2]. This now correctly orders the average by Date2 which is how the columns in my visual are ordered.

My last issue however is that Date2 can be the same date against multiple ID's. With this solution it is grouping all the ID with the same Date2 together and re-calulating the average as a group rather than individually for each ID as I would like. So the FILTER needs to be amended to FILTER by Date2 then by ID.