Calculate MOVING AVERAGES using DATESINPERIOD DAX Function // DAX Basics Guide in Power BI
Vložit
- čas přidán 31. 07. 2024
- Get Demo Files here
ko-fi.com/s/a17ecb6cd9
In this video were going to cover how to the DATESINPERIOD DAX function to calculate moving averages very easily in Power BI.
Link to Documentation
docs.microsoft.com/en-us/dax/...
-
🚩 Get Demo Files here
bit.ly/3dJE2O7
👍 Become a Patreon to support the channel and get free perks!
/ solutionsabroad
🛍 Get exclusive templates built using best practices here
ko-fi.com/solutionsabroad/shop
solutionsabroad.co.uk/store
❤ Other ways to support
ko-fi.com/solutionsabroad
GET IN TOUCH
📺 Website - www.solutionsabroad.co.uk
🤵 LinkedIn - / solutionsabroad
📘 Facebook - / solutionsabroad
🌍 WHO IS SOLUTIONS ABROAD
Hi, my name is Fernan, the one-man band of Solutions Abroad. I work as a Data Scientist based in London, UK and have over 6 years of experience working in Business Intelligence. Solutions Abroad these days is dedicated to teaching the ins and outs of Power BI, through the power of CZcams.
If you want to learn more, I’ve conveniently created playlists to help you find the right topic for you.
🐎 First, watch this video about how a typical Power BI workflow looks like
• Power BI from START to...
🗺 Learn DAX to extend your Power BI calculations
• Power BI DAX Basics OLD
📆 Stay up-to-date on all the features Power BI releases monthly
• Power BI Monthly Featu...
🧪 Get certified as a Microsoft Data Analyst Associate
• Power BI DA 100 Prepar...
💡 Learn Power BI Features to improve your dashboards
• Power BI Feature Highl...
📊 WHAT IS POWER BI?
Power BI is a business analytics tool by Microsoft. It creates a working environment suited for normal users, meaning tasks such as data extraction, cleansing, analysis and storytelling; these are made easily-accessible to citizen-users, and extendable for technical users. At face value it provides visually-stunning, interactive dashboards, but it’s built on top of so much more.
🔻 Get started with Power BI Desktop for FREE
powerbi.microsoft.com/en-us/d...
#PowerBI #DataAnalytics #BusinessIntelligence
I spent MONTHS looking for this solution!!! I'm still a DAX rookie. Thankyou Very Much!!!
Thanks for all you do and represents sir Fernan
Very useful and easy to learn! Thank you for sharing your knowledge!!! :)
You're very welcome!
Great video. Thank you so much.
Great explanation. Thanks !
Thanks for this. Nice straightforward explanation !
Glad it was helpful!
Very easy to follow your teaching! Many thanks for your time and effort!
You're very welcome!
Awesome video 😊 thanks so much .
Super useful for varius KPIs.
very Useful..
Awesome Video
Usefull and helpful 👍
Thanks!
YOU THE MAN
Finished watching
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!
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?
Thank you for great clarity in rolling average! How can it be achieved by using AverageX function with DatesInPeriod?
hello. Thanks for this vidéo.
with this function, can we compare vs 1st January for example?
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?
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 :(
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.
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?
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!!!
Hi sir is there any possibility of by selecting April month the previous 3months average should be shown. Kindly please 🙏 me with this
Can you explain why we are using max date
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
Can we do the same by dateadd dax ?
What if you do min() instead of max(), will the result be same?
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 :)
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
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!
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 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: czcams.com/video/dR-XmJil5DM/video.html
Thanks for all you do and represents sir Fernan
Appreciate your kind words sir