Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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?
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
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?
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 |
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
109 | |
89 | |
76 | |
67 |
User | Count |
---|---|
125 | |
111 | |
100 | |
83 | |
71 |