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 all, I have a question about the following:
I have a table, in which MON turnover is mentioned on 04/01/2018 (117.000)
Now I want to add a column, in which the average MON turnover per day is automatically calculated.
Can anybody help me with the DAX formula for this?
Thanks!
John
Date Turnover MON Average Turnover per day MON
01/01/2018 0 29.250
02/01/2018 0 29.250
03/01/2018 0 29.250
04/01/2018 117.000 29.250
Solved! Go to Solution.
Hi Ashish,
I tried it and this is exactly what I need.
Thanks a lot!
John
Hi,
Try this calculated column formula
=CALCULATE(MAX(Data[Turnover MON])/COUNTROWS(Data),ALL(Data))
Hope this helps.
Good day Ashish,
Thanks for your reply, it helps me a lot!
However, now I face a new challenge.
If I want to use this formula over a certain period, can I do that as well?
for example: I want to use this formula for the january data and seperately or for february data
Thanks upfront for your help in this,
John
Date Turnover MON Average Turnover per day MON
01/01/2018 0 29.250
02/01/2018 0 29.250
03/01/2018 0 29.250
04/01/2018 117.000 29.250
03/02/2018 0 70.000
04/02/2018 210.000 70.000
05/02/2018 0 70.000
Hi,
I created 2 calculated columns
Month-Year = FORMAT(Data[Date],"mmm-yy")
Result = CALCULATE(SUM(Data[Turnover Mon]),FILTER(Data,Data[Month-Year]=EARLIER(Data[Month-Year])))/CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Month-Year]=EARLIER(Data[Month-Year])))
Hope this helps.
Hi Ashish,
I tried it and this is exactly what I need.
Thanks a lot!
John
You are welcome. If my reply helped, please mark it as Answer.
Hi @jwi1,
As a measure, you can simply use
Avg Turnover MON - Measure = AVERAGE ( Table[Turnover MON] )
and then place this measure in a table with dates. The measure will be automatically filtered based on the date.
As a calculated column, you need to use CALCULATE() function to specify a filter
Avg Turnover MON - Column = CALCULATE ( AVERAGE ( 'Table'[Turnover] ), /* This is the filter telling DAX to
"For the whole table, get me the average of Turnover MON
if the date is equal to this one." */ ALL ( 'Table' ), 'Table'[Date] = EARLIER ( 'Table'[Date] ) )
Alternatively, you can also use the fomula below which result is the same as the one above.
Avg Turnover MON - Column2 = CALCULATE ( AVERAGE ( 'Table'[Turnover] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
Proud to be a Super User!
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |