I hope you enjoyed this tutorial 😃 If you did please give it a LIKE... and don’t forget to SUBSCRIBE for even more content! For More 👉 ruclips.net/user/EssentialExcel
I was struggling to achieve the same result by experimenting with AVERAGEX... It's brilliant how a simple divide by RollingPeriod helped! Many thanks! Subscribed!
Hey!! The solution and approach looks great! But the drawback of this approach is this would not give proper average if the dates aren't continuous i.e. if the sales data for a few days are missing in the data. The formula would sum up properly until 7 days, but you cannot divide this sum by 7 as the number of entries considered are not 7 in case of missing data.
How can I keep the rolling average for 1st entry the same as 'Amount' and then start calculating the average for the second entry as Rolling sum/2, for third entry as rolling sum/3 and so on....
Great, no unnecessary jazz and an accent which neither grates nor is difficult to understand. Also that conversion from pesky default date hierarchy -> flat date at 1:52, I honestly didn't know that and I've produced dozens of reports.
Trying to calculate the 12 month rolling average for a rate (Measure). It doesn't seem to be working and is just returning the same value as the current month's value (not the average of the last 12 months).
I'm trying to do this but with number of visits my field team does. Instead of "Sum[Total Sales]" I do "DistinctCount[VisitID]" but when I drop the Moving Average measure into my line chart, it looks exactly the same as if I would drop the [VisitID] column into it. No average is being calculated. It just shows the daily visits. What am I missing?
How would I do a rolling 7 days Calculation if I were to combine it with a Filter formula? For example Measure Name = VAR NumDays = 7 VAR RollingCOUNT = CALCULATE( COUNT ( 'table'[ColumnName],FILTER(table,'table'[column]="Value") DATESINPERIOD( table[Date],LASTDATE(table[Date]),-Numdays,DAY) ) RETURN RollingSum
Sorry! At time stamp 04:25 - you are using the CALCULATE function and inside it you are calculating the SUM of the past seven days amount. So, my question is, you are doing just normal SUM here then what is the purpose of using the CALCULATE function here?@@EssentialExcel
I don’t seem to fully understand how LASTDATE works in this formula. I know LASTDATE returns a table with one row and colum of the last date of the specified column date. I’m this example the last date is July 23rd. The calculate function will run whatever function we specified for each row. So how is it the each row isn’t just based on July 23rd? Or is it the LASTDATE is based on the last date for the specific row it’s evaluating at a specific point in time. So when it’s evaluating row 1, the Last date is July 1 bc it hasn’t evaluated the rest. So row 2 the last date is July 2 bc between row 1 and 2 that’s the oldest date and so on until it get to the last row. Any clarification would be appreciated.
You pretty much answered your own question. The second part of your comment describes exactly how last date works it always depends on the row context unless you specify it in the calculate filter statement(like all (calender) or remove filters.
@@sonustar9077 hi Sonu. Thanks for your explantion on this. Since the Lastdate function is evaluated on each row context and is basically a date picker for each row, in that sense Lastdate and Firstdate does not make difference. So we can change the LASTDATE function to FIRSTDATE function in the dateinperion expression and this will not affect the result. Is that correct? Thanks for your further check and confirmation.
I hope you enjoyed this tutorial 😃 If you did please give it a LIKE... and don’t forget to SUBSCRIBE for even more content!
For More 👉 ruclips.net/user/EssentialExcel
You get a subscription for this! Exactly what I needed minus the fluff that I have found in other tutorials. Excellent stuff!
I was struggling to achieve the same result by experimenting with AVERAGEX... It's brilliant how a simple divide by RollingPeriod helped!
Many thanks! Subscribed!
Thank you Lukasz! Glad it gave you the answer to your problem! 😃
Thank you -- good example!
This is great, easy to understand, simple and concise 👍👍
in this example if i want to exclude the weekends how can i do?
Thank You So much for the insight given to us through your tube channel. 🙏
Glad they are helpful! Thank you Mariomds 🙂
It's a great video. My question is whether I can calculate the trailing average for the Measure I created.
Hi Miao 👋 technically yes, you could structure a new measure using this measure as the input.
Hey!! The solution and approach looks great! But the drawback of this approach is this would not give proper average if the dates aren't continuous i.e. if the sales data for a few days are missing in the data. The formula would sum up properly until 7 days, but you cannot divide this sum by 7 as the number of entries considered are not 7 in case of missing data.
How do i do rolling avg of months.
while the month rage will be selected from slicer
So i select july 2020 to date and i get rolling avg of amount
Thanks for sharing . Amazing
How can I keep the rolling average for 1st entry the same as 'Amount' and then start calculating the average for the second entry as Rolling sum/2, for third entry as rolling sum/3 and so on....
Thank you so much!!! 😃
Thank you!
Great, no unnecessary jazz and an accent which neither grates nor is difficult to understand.
Also that conversion from pesky default date hierarchy -> flat date at 1:52, I honestly didn't know that and I've produced dozens of reports.
Thank you and glad you enjoyed the video! 😃
Thank you for that 👍
Welcome Bakhos 🙂
Trying to calculate the 12 month rolling average for a rate (Measure). It doesn't seem to be working and is just returning the same value as the current month's value (not the average of the last 12 months).
I wish you would zoom in on the formula ... it's super blurry on my view and very difficult to read the text in the rolling average measure.
Thank you!🙏🏻
Thank you!
Are you also able to calculate the correct amount the first 7 or 28 days?
Amazing 👏
thx saved me!
I'm trying to do this but with number of visits my field team does. Instead of "Sum[Total Sales]" I do "DistinctCount[VisitID]" but when I drop the Moving Average measure into my line chart, it looks exactly the same as if I would drop the [VisitID] column into it. No average is being calculated. It just shows the daily visits.
What am I missing?
Same
It's because of distinctcount the ID is only counted one time in the period cause the ID is unique.
How would I do a rolling 7 days Calculation if I were to combine it with a Filter formula?
For example
Measure Name =
VAR NumDays = 7
VAR RollingCOUNT =
CALCULATE( COUNT ( 'table'[ColumnName],FILTER(table,'table'[column]="Value")
DATESINPERIOD( table[Date],LASTDATE(table[Date]),-Numdays,DAY)
)
RETURN
RollingSum
thanks 👍
Thank you 🙂
Just curious why you are using the CALCULATE function given that you are not using any FILTER! at time stamp 05:40
Hello 👋. Not sure I follow your question as this is when we enter our filter criteria using
DATESINPERIOD?
Sorry! At time stamp 04:25 - you are using the CALCULATE function and inside it you are calculating the SUM of the past seven days amount. So, my question is, you are doing just normal SUM here then what is the purpose of using the CALCULATE function here?@@EssentialExcel
I don’t seem to fully understand how LASTDATE works in this formula. I know LASTDATE returns a table with one row and colum of the last date of the specified column date. I’m this example the last date is July 23rd. The calculate function will run whatever function we specified for each row. So how is it the each row isn’t just based on July 23rd? Or is it the LASTDATE is based on the last date for the specific row it’s evaluating at a specific point in time. So when it’s evaluating row 1, the Last date is July 1 bc it hasn’t evaluated the rest. So row 2 the last date is July 2 bc between row 1 and 2 that’s the oldest date and so on until it get to the last row. Any clarification would be appreciated.
have the same query. I am wondering if you have this clarified. Thanks
@@kebincui haven’t clarified this yet, sorry :s
You pretty much answered your own question. The second part of your comment describes exactly how last date works it always depends on the row context unless you specify it in the calculate filter statement(like all (calender) or remove filters.
@@sonustar9077 hi Sonu. Thanks for your explantion on this. Since the Lastdate function is evaluated on each row context and is basically a date picker for each row, in that sense Lastdate and Firstdate does not make difference. So we can change the LASTDATE function to FIRSTDATE function in the dateinperion expression and this will not affect the result. Is that correct? Thanks for your further check and confirmation.
This works fine between dates, but I don't see how it would work if you just wanted a cumulative average.