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.
Hi,
I would like to reproduce this table, made in excel, in DAX.
ID | DATE_PERIOD | MP_PLAN_DELTA | Average |
1 | 27.10.19 | 3 | =AVERAGE(C2;C4;C6) |
2 | 27.10.19 | 5 | =AVERAGE(C3;C5;C7) |
1 | 28.11.19 | 6 | =AVERAGE(C4;C6;C8) |
2 | 28.11.19 | 8 | =AVERAGE(C5;C7;C9) |
1 | 29.12.19 | 7 | =AVERAGE(C6;C8;C10) |
2 | 29.12.19 | 5 | =AVERAGE(C7;C9;C11) |
1 | 30.01.19 | 6 | … |
2 | 30.01.19 | 8 | … |
1 | 01.02.19 | 5 | .. |
2 | 01.02.19 | 7 | .. |
1 | 02.03.19 | 3 | … |
2 | 02.03.19 | 6 | … |
The aim is to obtain the average value over the 3 months and progressively continue.
I'm trying to use this command:
CALCULATE(SUM(COPR_DETAIL[MP_PLAN_DELTA]);DATESINPERIOD(COPR_DETAIL[DATE_PERIOD].[Month];FIRSTDATE(COPR_DETAIL[DATE_PERIOD].[Month]); 3; MONTH))
It's not good.
do you have any ideas ?
Marco
Solved! Go to Solution.
Hi,
Thank you for your answer. The problem I am facing is to do the spread over the three months.
Now I'm trying to use this formula:
MM - ManMonths (Media - I Round)_MM = CALCULATE(AVERAGE(MM[MP_PLAN_DELTA_MM]);DATESINPERIOD(MM[DATE_PERIOD].[Date];MM[DATE_PERIOD]; 2;MONTH))
It seems to be fine in the total picture. As soon as I try to use some filters, it no longer makes sense.
Can I send you the file so you can actually see what my problem is?
Dom
@Anonymous ,
To be general, you may modify the measure like pattern below:
Result = VAR Current_Month = MONTH ( COPR_DETAIL[DATE_PERIOD] ) VAR Previous_Month = Current_Month - 3 RETURN CALCULATE ( SUM ( COPR_DETAIL[MP_PLAN_DELTA] ); FILTER ( COPR_DETAIL; COPR_DETAIL[DATE_PERIOD] <= Current_Month && COPR_DETAIL[DATE_PERIOD] >= Previous_Month ) )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you for your answer. The problem I am facing is to do the spread over the three months.
Now I'm trying to use this formula:
MM - ManMonths (Media - I Round)_MM = CALCULATE(AVERAGE(MM[MP_PLAN_DELTA_MM]);DATESINPERIOD(MM[DATE_PERIOD].[Date];MM[DATE_PERIOD]; 2;MONTH))
It seems to be fine in the total picture. As soon as I try to use some filters, it no longer makes sense.
Can I send you the file so you can actually see what my problem is?
Dom
@Anonymous ,
@Anonymous wrote:
Hi,
Thank you for your answer. The problem I am facing is to do the spread over the three months.
Now I'm trying to use this formula:MM - ManMonths (Media - I Round)_MM = CALCULATE(AVERAGE(MM[MP_PLAN_DELTA_MM]);DATESINPERIOD(MM[DATE_PERIOD].[Date];MM[DATE_PERIOD]; 2;MONTH))It seems to be fine in the total picture. As soon as I try to use some filters, it no longer makes sense.
Can I send you the file so you can actually see what my problem is?Dom
Which field does the filter apply on? To be general, you can use function ALLSELECTED() to apply the filters in your measure. You can upload the sample file to onedrive.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I solved the problem with the DATESINPERIOD formula I used 60 days instead of 2 months.
I don't know the problem but it is the solution.
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 |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |