Adding Weekday average line into stacked column chart

[chili]

Hi, I am trying to add a weekday average line into a stacked column chart, but I cannot find a solution so far

Here is my table

Date	Team	value	Weekday
4/8/2022	a	10	Fri
4/8/2022	b	20	Fri
4/8/2022	c	30	Fri
.	.	.	.
4/15/2022	a	10	Fri
4/15/2022	b	12	Fri
4/15/2022	c	30	Fri
4/16/2022	a	10	Sat
4/16/2022	b	73	Sat
4/16/2022	c	16	Sat
4/17/2022	a	22	Sun
4/17/2022	b	45	Sun
4/17/2022	c	50	Sun

I want to calculate the average by the weekday over the last weeks, for example, on 15 April (Fri), I get the average of the last two Fridays, 1st April and 8th April. on 16 April (Sat), I get the average of the last two Saturdays, 2nd April and 9th April. Then I can add this as a line in the chart, to compare the performance of the current weekday.

It would be great to get any suggestions or help from you!

[v-henryk-mstf · Accepted solution]

Hi @chili ,

According to your description, does the following test result meet your requirements.

M =
VAR cur_ =
    SELECTEDVALUE ( 'Table'[value] )
VAR day1 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] - 7 = cur_ )
VAR day2 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] - 14 = cur_ )
VAR day3 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] = cur_ )
RETURN
    ( day1 + day2 + day3 ) / 3

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[v-henryk-mstf · Accepted solution]

Hi @chili ,

According to your description, does the following test result meet your requirements.

M =
VAR cur_ =
    SELECTEDVALUE ( 'Table'[value] )
VAR day1 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] - 7 = cur_ )
VAR day2 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] - 14 = cur_ )
VAR day3 =
    CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] = cur_ )
RETURN
    ( day1 + day2 + day3 ) / 3

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[chili · Accepted solution]

Hello, sorry for the late reply, I was testing out different solutions, but unfortunately the measure above cannot return the correct average number each weekday. I made few changes which inspired by your solution, and I finally got the right numbers for that:

First I created two measures:

1_week before sum =
VAR __max = MAX('Table'[date])
RETURN
calculate(SUMX('Table', 'Table'[value]),'Table'[date]=__max-7)

and

2_week before sum =
VAR __max = MAX('Table'[date])
RETURN
calculate(SUMX('Table', 'Table'[value]),'Table'[date]=__max-14)

then I simply get the average by creating the third measure

2 weeks average = ([1_week before sum] + [2_week before sum]/ 2 

Finally, I used 2 weeks' average to be the value in the constant line, like what you did before. That's how I got the weekday average. Thanks again for your help!

[Adescrit]

Hi @chili ,

You could try a measure like this:

Average Last two weeks =
CALCULATE (
    AVERAGE ( 'Table'[value] ),
    FILTER (
        'Calendar',
        'Calendar'[Weekday] = SELECTEDVALUE ( 'Calendar'[Weekday] )
            && 'Calendar'[Date]
                >= SELECTEDVALUE ( 'Calendar'[Date] ) - 14
    )
)

Hopwever, whether it works or not I think depends on whether you are filtering one or all teams. Let me know.

[chili]

Hi @Adescrit 

thanks for your reply. I tried to make this measure, but it doesn't return anything (no line in the chart or empty column in a table). Here is my data schema:

I highly appreciate your help!

[chili]

Hi, is there anyone who could help? I tried the solution in this post

But it doesn't work for my report