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'm trying to add moving average to a measure i created,
but the moving average should ignore blank rows.
for example:
Date Value
1/1/2017 10
1/2/2017 5
1/3/2017
1/4/2017 8
The moving average 3 (last 3 days) of 1/4/2017 should be (8+5+10)/3 = 7.666
What i have now is, for moving average 3 of 1/4/2017 : (8+5)/2 = 6.5
How can i filter the blank rows before the calculation of the average?
This is the DAX code of the measure:
MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
[DiffFirstSecondAvgOHLC])
DimDate includes all the dates from 1/1/2016 till now.
DiffFirstSecondAvgOHLC is a measure that subtracts two other measures, that don't have values for 1/3/2017 for example.
The dates that don't have value are not trading days, i have a boolean column [IsTradingDay].
Any help will be appriciated.
Thanks,
Eran
Solved! Go to Solution.
Thanks for you answer,
I solved it already in different way, but i'll save your solution for any case.
The solution that worked for me is:
adding incremental column to the date, which gives id only to the trading days, and another column which is the id - 3 (for the moving average).
then add a measure:
AVERAGEX(FILTER(ALL(DimDate),[Rank] > MAX(DimDate[RankPrev3]) && [Rank] <= MAX(DimDate[Rank])), [AverageColumn])
Eran
Hi eranmn
Can I ask why you are using AVERAGEX to calculate moving average?
Best
Martin
Hi,
I read it in another forum.
What is your suggestion?
Eran
I saw in your earlier post that you want it based on days. So here is the example:
First you need to calculate the average of your target: Average of target = AVERAGE(Table[Target])
Then you calculate the moving average like this: Moving average of target = CALCULATE([Average of target];DATESINPERIOD(Table[Date];LASTDATE(Table[Date]);-3;DAY))
This is without a time dimension. You could also do this with a time dimension. It makes no difference.
I've attached a picture with the solution. Let me know if you want me to elaborate on anything.
Best
Martin
Thanks for the example,
but my problem is more complicated,
i need to create moving average to measure, not column,
and the dates column including rows that should be filtered before the average taking place.
Can you elaborate? As far as you explain for now, my solution will work for you. Maybe you can provide an example for clarification?
You can check my original query, i wrote example,
plus, look at this article:
This is exactly my problem, but i have measure instead of calculated column.
Eran
I understand. Sorry, I missed this part of your problem. I can see that it will be visually misrepresented.
So you need to do something else. This method can be applied to both measures and columns.
- Create your measure: OriginalMeasure = SomeMeasure
- Create a measure, which defines blanks: MeasureWithBlanks = IF(ISBLANK([OriginalMeasure]);"";[OriginalMeasure])
- Create your moving average measure: OriginalMovingAvg = CALCULATE([OriginalMeasure]; DATESINPERIOD(Table1[Date];LASTDATE(Table1[Date]);-3;DAY))
- Create a measure, which excludes blanks: MovingAvg = IF(ISBLANK([MeasureWithBlanks]);"";[Moving average1 of Value])
This looks a little silly if you plot it in a trend-visualization (see picture below), because the measure doesn't calculated anything for the missing day. You can go to format and change the x-axis to continous. This will give a smoother interpretation, but it will misrepresent the x-axis.
Let me know if this solves your problem.
Thanks for you answer,
I solved it already in different way, but i'll save your solution for any case.
The solution that worked for me is:
adding incremental column to the date, which gives id only to the trading days, and another column which is the id - 3 (for the moving average).
then add a measure:
AVERAGEX(FILTER(ALL(DimDate),[Rank] > MAX(DimDate[RankPrev3]) && [Rank] <= MAX(DimDate[Rank])), [AverageColumn])
Eran
@Anonymous
I'm glad you solved it. Clever way to define which days has values and which hasn't. Could I ask you to choose either one of our solutions as an "accepted solution" for the thread, so we can help people with similar challenges?
Best of luck in the future
Martin
@Anonymous isn't it better practice to use a time dimension though when using date functions?
Proud to be a Super User!
@vanessafvg Yes, I agree. I always use it myself and I would always advice people to use time dimensions. The note was simply to emphasis that I didn't use it in the test-example.
@Anonymous
Is my solution working for you?
I suggest you use calculate instead. I'll make an example for you but first i need to know one thing: Do you want to make moving average based on day, week, month, quarter or something else?
Bestt
Martin
Thanks,
On day.
@Anonymous
which part is blank?
not sure if this will work but give it a bash
MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
not(blank([DiffFirstSecondAvgOHLC])))
Proud to be a Super User!
Hi,
Thanks for your answer, but unfortunately it's not working,
the blank function ahould be IsBlank, but the UI gives error :
"The function AVERAGEX takes an argument that evaluates to numbers or dates and cannot work with values of type Boolean."
Eran
@Anonymous actually there is blank() and isblank
probably best to add filter to it,
MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
calculate([DiffFirstSecondAvgOHLC], DiffFirstSecondAvgOHLC >= 0)
or something to that affect
Proud to be a Super User!
I tried:
your suggestion gives error:
"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
calculate([DiffFirstSecondAvgOHLC], FILTER(VIXFuturesData, [DiffFirstSecondAvgOHLC] <> 0)))
but it give for every row the value of [DiffFirstSecondAvgOHLC], didn't calculate the average.
sorry 😞
probably best to use filter then, did the one you did work?
MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
calculate([DiffFirstSecondAvgOHLC], FILTER(VIXFuturesData, [DiffFirstSecondAvgOHLC] <> 0)))
Proud to be a Super User!
no,
it gives for every row the value of [DiffFirstSecondAvgOHLC], didn't calculate the average.
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |