Hello Fernan. For January month we don't have values for previous two months and while calculating moving average you took value as 3 for dividing is this correct approach?
Nice and concise, thank you ❤. A simple divide is perfect. I tried to use AVERAGE in place of SUM (may data has only 1 entry per month) - but it returned same as SUM ??? Can you explain why Average doesnt work here?
Hi, I am very new to Power BI and I am trying to get a Dax that shows me a summary of the PREVIOUS 3 months (NOT including current month - based on current date) without having to select the month from a slicer or table (essentialy the summary in a card). I have both past and future forecast dates in my data so I keep tripping up on the Max Date in Period part of the calculation. Can you perhaps help me with that please? Thank you in advance!! Also, thank you for all your videos, as I have learned so much from watching your videos. 🙂
Thanks so much for this, helped tremendously! Was struggling with a "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column" error when attempting to use the Quick Measure function previously.
Great video thanks so much for this! I have tried the formula on excel power pivot ( not Dax) but it it came up with error mdsscript 6,73 cannot convert value " of the text to numeric. I wonder if u have this type of error before and how did u resolve it? Thanks!
Glad it helped! Never had this one, maybe one of you numeric columns had a text in it? Check your steps and where it errors, that's where I would look :)
what are the moving average numbers for February 2020 & January 2020 here ? How did they come about when there is no data from 2019 to calculate the moving avg backwards?
Thank you for the video - In my report the MAX function returns all dates regardless of whether there is a number in the Views table for that date. But I am able to achive the results using the LAST Date Function - Would you know why that would possibly happen?
Hi there, there could be many things that affect this, it could be the current context you're in, or if its a column or measure. I'd use LASTDATE for consistency though as MAX can be used for non date-type columns like numbers
Hi, I have created a Matrix chart in power bi, which has last 5days date as column, departments as rows and sales data as values. I want a single column to display average of my sales in a single column for each row to check what is the average sales in last 5 days for that particular department. Please make a video on this ASAP
Hi and thank you for the video!! Please can you show how to calculate a monthly average based on the monthly values of last 5 years. For example: July 2023 value = (July2022+July2021+July2020+July2019+July2018)/5 Thanks!!!
Hiya, no worries! It sounds like you'll need to create a new column with a PREVIOUSMONTH DAX function to get the previous month expression, if you need help I cover it here: ua-cam.com/video/dR-XmJil5DM/v-deo.html
Thanks so much for this video. Excellent 👍👍🌹🌹. But I have 2 queries and would appreciate if could check and help me to understand. The first is for Datesinperiod function, 3 months back from the last day (1 January 2026) should be 1 October 2025 . This should around 90 days. In your video 3 months back from is this example 3 months back is 1 November 2025, which is 62 days. Your calender table is complete for the date range and I could not figure out why 3 month back is 1 Nov 2025. My second query is for 3 month moving average calcualtion, I feel it would be better to directly use average function rather than using 3 months' sum divided by 3 because for the first 2 months, the moving average figures are distorted since it is 1 or 2 months sum divided by 3. Could you please kindly check and clarify my queries. Thank you very much.
I spent MONTHS looking for this solution!!! I'm still a DAX rookie. Thankyou Very Much!!!
Very easy to follow your teaching! Many thanks for your time and effort!
You're very welcome!
Thanks for this. Nice straightforward explanation !
Glad it was helpful!
you are great as usual thanks
hello. Thanks for this vidéo.
with this function, can we compare vs 1st January for example?
Thank you for great clarity in rolling average! How can it be achieved by using AverageX function with DatesInPeriod?
Hey, thank you for all of that! But where did you get the Year / Month column from? Cannot find the description how to group to this column :(
Hello Fernan.
For January month we don't have values for previous two months and while calculating moving average you took value as 3 for dividing is this correct approach?
Super useful for varius KPIs.
Nice and concise, thank you ❤. A simple divide is perfect. I tried to use AVERAGE in place of SUM (may data has only 1 entry per month) - but it returned same as SUM ??? Can you explain why Average doesnt work here?
Thanks for all you do and represents sir Fernan
Hi, nice one. Have a doubt like for jan 2020 and feb 2020 we don’t need to divide by 3 months. How to do it. Can we make it dynamic count of month.
Very useful and easy to learn! Thank you for sharing your knowledge!!! :)
You're very welcome!
Hi, I am very new to Power BI and I am trying to get a Dax that shows me a summary of the PREVIOUS 3 months (NOT including current month - based on current date) without having to select the month from a slicer or table (essentialy the summary in a card). I have both past and future forecast dates in my data so I keep tripping up on the Max Date in Period part of the calculation. Can you perhaps help me with that please? Thank you in advance!! Also, thank you for all your videos, as I have learned so much from watching your videos. 🙂
Awesome video 😊 thanks so much .
Great video. Thank you so much.
Thanks.
How to make measure for moving average with selected calendar slicer.
Hi Rehan, I'm not sure exactly if it answers you exactly but you'll need to tweak the DATESINPERIOD values to match with your calendar slicer!
Great explanation. Thanks !
Thanks so much for this, helped tremendously! Was struggling with a "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column" error when attempting to use the Quick Measure function previously.
Glad it helped! It's really annoying that quick measures only work on Auto date/time, it would've been a lifesaver!
Great video thanks so much for this! I have tried the formula on excel power pivot ( not Dax) but it it came up with error mdsscript 6,73 cannot convert value " of the text to numeric. I wonder if u have this type of error before and how did u resolve it? Thanks!
Glad it helped! Never had this one, maybe one of you numeric columns had a text in it? Check your steps and where it errors, that's where I would look :)
Great Info
Can we do the same by dateadd dax ?
what are the moving average numbers for February 2020 & January 2020 here ? How did they come about when there is no data from 2019 to calculate the moving avg backwards?
Hi sir is there any possibility of by selecting April month the previous 3months average should be shown. Kindly please 🙏 me with this
Finished watching
Can you explain why we are using max date
thanks a lot
Usefull and helpful 👍
Thanks!
YOU THE MAN
Thank you for the video - In my report the MAX function returns all dates regardless of whether there is a number in the Views table for that date. But I am able to achive the results using the LAST Date Function - Would you know why that would possibly happen?
Hi there, there could be many things that affect this, it could be the current context you're in, or if its a column or measure. I'd use LASTDATE for consistency though as MAX can be used for non date-type columns like numbers
Hi,
I have created a Matrix chart in power bi, which has last 5days date as column, departments as rows and sales data as values. I want a single column to display average of my sales in a single column for each row to check what is the average sales in last 5 days for that particular department. Please make a video on this ASAP
What if you do min() instead of max(), will the result be same?
very Useful..
Awesome Video
Hi and thank you for the video!!
Please can you show how to calculate a monthly average based on the monthly values of last 5 years.
For example: July 2023 value = (July2022+July2021+July2020+July2019+July2018)/5
Thanks!!!
I watched this a lot of times coz it’s so useful for my work. But what if the start date is from last month? Pls help thanks
Hiya, no worries! It sounds like you'll need to create a new column with a PREVIOUSMONTH DAX function to get the previous month expression, if you need help I cover it here: ua-cam.com/video/dR-XmJil5DM/v-deo.html
Thanks so much for this video. Excellent 👍👍🌹🌹. But I have 2 queries and would appreciate if could check and help me to understand. The first is for Datesinperiod function, 3 months back from the last day (1 January 2026) should be 1 October 2025 . This should around 90 days. In your video 3 months back from is this example 3 months back is 1 November 2025, which is 62 days. Your calender table is complete for the date range and I could not figure out why 3 month back is 1 Nov 2025. My second query is for 3 month moving average calcualtion, I feel it would be better to directly use average function rather than using 3 months' sum divided by 3 because for the first 2 months, the moving average figures are distorted since it is 1 or 2 months sum divided by 3. Could you please kindly check and clarify my queries. Thank you very much.
This is not a moving average, it is a moving sum.
Thanks for all you do and represents sir Fernan
Appreciate your kind words sir