I want to get the average and maximum value of the average before the selected month in slicer

[kai1109]

As the title says.
Please advise how to implement it

name	date(month)	value
A	Apr-23	200
B	Apr-23	300
C	Apr-23	400
D	Apr-23	500
A	May-23	150
B	May-23	250
C	May-23	350
D	May-23	450
A	Jun-23	50
B	Jun-23	60
C	Jun-23	70
D	Jun-23	80
A	Jul-23	90
B	Jul-23	100
C	Jul-23	110
D	Jul-23	120
A	Aug-23	555
B	Aug-23	555
C	Aug-23	555
D	Aug-23	555
A	Sep-23	222
B	Sep-23	222
C	Sep-23	222
D	Sep-23	222

If you have data like this, let's say you select June in the slicer.
I would like to display the results in the table below.

name	date(month)	value
A	Jun-23	133
B	Jun-23	203
C	Jun-23	273
D	Jun-23	343

The logic is that if you select June in the slicer, the average value for each name will be calculated as April, May and June, and May and June.
I want to display the maximum value among them.

For example, if June is selected with the slicer, the average of June and May is 100, and the average of June, May, and April is 133. Therefore, I would like to display 133.

And there are some conditions.
・You can go back up to 6 months.
・I want to calculate it by fiscal year, so it starts in April and ends in March.
・So, in the case of April, the average is not calculated, and in the case of March, the above calculation is performed using data up to October.

please give me a good idea

[v-heq-msft · Accepted solution]

Hi @kai1109 ,

Thanks to @Musadev  for the solution.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a date table

Date = VALUES('Fact'[date(month)])

Create a measure

Maximum of two averages = 
VAR sum_3 = 
CALCULATE(
    SUM('Fact'[value]),
    FILTER(
        'Fact',
        'Fact'[date(month)] <= SELECTEDVALUE('Date'[date(month)]) && 'Fact'[date(month)] >= EDATE(SELECTEDVALUE('Date'[date(month)]),-2)
        )
    )
VAR sum_2 = 
CALCULATE(
    SUM('Fact'[value]),
    FILTER(
        'Fact',
        'Fact'[date(month)] <= SELECTEDVALUE('Date'[date(month)]) && 'Fact'[date(month)] >= EDATE(SELECTEDVALUE('Date'[date(month)]),-1)
    )
)
VAR average_2 = DIVIDE(sum_2,2)
VAR average_3 = DIVIDE(sum_3,3)
RETURN
IF(
    average_2 > average_3,
    average_2,
    average_3
)

Use date table as the value of slicer
Fianl output

Best regards,

Albert He

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

[kai1109]

Thank you for the very helpful calculation formula.
I tried to reflect this in actual data, but it didn't work.
I think that the calculation results are probably incorrect because it needs to be calculated starting in April.
For example, when I select April in the sample data I received, the results are also incorrect.
I use TOTALYTD in other calculation formulas, is it possible to incorporate it into this calculation formula as well?

[v-heq-msft]

Hi @kai1109 ，
Thank you for your reply, according to your description you want to end the fiscal year with a custom time, you can certainly customize the fiscal year cycle with the parameters of TOTALYTD.

Best regards,

Albert He

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

[kai1109]

sorry. It still didn't go well.
Please give me some more advice.

For production data, the date table and fact table need to connect relationships in order to take advantage of other formulas and graphs.

If relations are connected, when you select a slicer in the date table, the data will be filtered by that month, and the average maximum time will not reach the required value.

Is there any good solution?

[kai1109]

I would like to ask you to provide additional information.
In actual data,
Select the year and month separately using the slicer.
How should I modify the calculation formula in that case?
I added 

'Fact'[date(year)] <= SELECTEDVALUE('Date'[year])

to the FILTER part, but it didn't give me the desired result.

name	date(month)	year	value
A	4	2024	200
B	4	2024	300
C	4	2024	400
D	4	2024	500
A	5	2024	150
B	5	2024	250
C	5	2024	350
D	5	2024	450
A	6	2024	50
B	6	2024	60
C	6	2024	70
D	6	2024	80
A	7	2024	90
B	7	2024	100
C	7	2024	110
D	7	2024	120
A	8	2024	555
B	8	2024	555
C	8	2024	555
D	8	2024	555
A	9	2024	222
B	9	2024	222
C	9	2024	222
D	9	2024	222

[Musadev]

Hi @kai1109 
You need to create 2 measures to get the desired results. 

AvgValue = AVERAGEX(TBL1,TBL1[Value])

This will calculate the  average value and in the second measure you will get the 3 months average for any current month, 

Last 3 Months Avg = CALCULATE(
    [AvgValue],
    DATESINPERIOD(TBL1[Date],
    MAX(TBL1[Date]),
    -3,
    MONTH
    )
)

Check the results here .

If any post helps, please consider Accepting it as the solution to help other members find it more quickly.

If I misunderstand your needs or if you still have problems, please feel free to let us know. Thanks a lot!