Master Dynamic MoM & YoY % Variance DAX Measures in Power BI
ฝัง
- เผยแพร่เมื่อ 13 ธ.ค. 2024
- 📊Dynamic MoM & YoY % Variance in Power BI - A Step-by-Step DAX Tutorial
🚀 In this tutorial, I’ll walk you through creating dynamic Month-over-Month (MoM) and Year-over-Year (YoY) DAX measures step by step. Learn how to show variances between two periods in a clear, actionable way-a key UX/UI component for impactful Power BI reports.
🎓What You’ll Learn in This Power BI UX/UI Tutorial:
📊 Dynamic MoM % Measure: Build and tweak a flexible MoM variance measure step by step.
📈 Dynamic YoY % Measure: Transform your MoM measure into a rolling 12-month YoY measure-or any other custom period-with ease.
📅 Master the EOMONTH Function: Learn how to create flexible, rolling date ranges with this essential DAX function, explained in detail with examples.
📝 Dynamic Text Labels: Visually show the time periods your MoM and YoY measures reference so stakeholders always understand the insights.
🌟Why Watch This Tutorial?
This video teaches you how to craft flexible, dynamic variance measures in DAX, elevating your Power BI reports with clear and actionable insights. Perfect for delivering stunning UX/UI-designed dashboards with context that stakeholders will love!
🤝If you found this video helpful, don’t forget to:
🔔 Like, Subscribe, and Turn on Notifications to stay updated!
💬 Comment with your feedback or ideas for future tutorials.
💌 Share this video with others who would love to level up their Power BI skills.
💡 Bonus!
Want to practice with the exact dataset featured in this tutorial? Enroll in my course, "14 Days to Mastering Power BI UX/UI Design." This program gives you the tools to create stunning, next-level Power BI reports. ✨ Enroll Now: 👉 courses.nextle... 🚀
🔗Link to "Take your Power BI Cards to the Next Level - Full PBI UX/UI Tutorial "👉 • Take your Power BI Car...
🔗Link to DAX MoM and YoY measures used in this tutorial👉drive.google.c...
#PowerBI #DAXTutorial #DataVisualization #UXUIDesign #PowerBIDesign #PowerBIReports #NextLevelReports #VarianceMeasures
The indepth instructions you provided are phenomenal. I'm bookmarking this in my favorites.
Glad you found it helpful! 👍
This is brilliant. Excellent explanation. Very enjoyable video. I’d love to see your version of the calculate function
Appreciate it! I'm glad you found it helpful,... I'll definitely consider adding a video on Calculate in the near future 👍
Your animations combined with your description of what the DAX is doing (broken down into steps) is top notch. Multiple "thumbs up"!!
Q: If I have a full featured date table with fields like year offset, month offset, month start, month end etc, do you think this would make the DAX simpler and faster?
Yes absolutely. If you already have offset fields in your data table, you can simply reference those fields (examples below)
EXAMPLE 1
Gross Sales MoM% =
VAR _CurrentMonthSales =
CALCULATE(
[Gross Sales],
REMOVEFILTERS( DIM_Date ),
DIM_Date[Month Offset] = 0
)
)
VAR _PreviousMonthSales =
CALCULATE(
[Gross Sales],
REMOVEFILTERS( DIM_Date ),
DIM_Date[Month Offset] = -1
)
)
etc....
EXAMPLE 2 =
Gross Sales YoY% =
VAR _Current12MonthSales =
CALCULATE(
[Gross Sales],
REMOVEFILTERS( DIM_Date ),
DIM_Date[Month Offset] -12
)
)
VAR _Previous12MonthSales=
CALCULATE(
[Gross Sales],
REMOVEFILTERS( DIM_Date ),
DIM_Date[Month Offset] -24
)
)
etc...
Great method for both demonstrating and utilizing visual steps, thank you.💥💥
You're very welcome!
Superb explanation...
Thank you 😀
Great Video! I’d glad you to mention cases with partial month comparison. For ex: Today we are at 12/9/24 so the comparison should be 12/1-12/8 vs. 11/1-11/8 (and not in compare to whole November).
@alono2323 yep!..we'd just have to tweak a little bit. One approach I would say could be to capture the % of days that have elapsed within the latest available month in the dataset, and then multiply % by the previous month sales. There's many ways to write DAX code for time periods, but the below I believe would work:
Gross Sales MoM% (% Elapsed) =
VAR _LatestVisiblePeriod = MAX( DIM_Date[Reporting Period] )
VAR _CurrentMonthStart =
CALCULATE(
MIN( DIM_Date[Date] ),
DIM_Date[Reporting Period] = _LatestVisiblePeriod
)
VAR _CurrentMonthLatestDate =
CALCULATE(
MAX( DIM_Date[Date] ),
DIM_Date[Reporting Period] = _LatestVisiblePeriod
)
VAR DaysElapsedCurrentMonth = DATEDIFF( _CurrentMonthStart,_CurrentMonthLatestDate,DAY )
VAR EndofMonthDateForCurrentMonth = EOMONTH(_CurrentMonthStart,0)
VAR DaysinCurrentMonth = DATEDIFF( _CurrentMonthStart,EndofMonthDateForCurrentMonth,DAY )
VAR ElapsedPercentageCurrentMonth = DIVIDE(DaysElapsedCurrentMonth,DaysinCurrentMonth,0)
VAR _PreviousMonthSales=
CALCULATE(
[Gross Sales],
REMOVEFILTERS( DIM_Date ),
DATESBETWEEN(
DIM_Date[Date],
EOMONTH( _CurrentMonthStart, -2 ) + 1,
EOMONTH( _CurrentMonthStart, -1 )
)
) * ElapsedPercentageCurrentMonth
VAR _CurrentMonthSales =
CALCULATE(
[Gross Sales],
REMOVEFILTERS( DIM_Date ),
DATESBETWEEN(
DIM_Date[Date],
EOMONTH( _CurrentMonthStart, -1 ) + 1,
EOMONTH( _CurrentMonthStart, 0 )
)
)
VAR _Result =
IF(
_PreviousMonthSales > 0,
(_CurrentMonthSales - _PreviousMonthSales ) / _PreviousMonthSales, BLANK() )
RETURN _Result
Sick! Thanks 🎉
Glad you like it!
@@nextlevelpowerbireports And would really like to see your video of explaining the calculate function :)
That's awsome! thanks
Glad you like it!
Hi! Thanks for the great videos you are creating! Is it possible to have one video explaining how to create a customized filter pane that can be displayed and hidden by the user to avoid to loose screen space?
Great suggestion!
Will u maybe in the future record tutorial for doing this report end to end?
@@jelenadj7289 Hello! You can follow along and build this exact report from scratch in my course, (in the description of this video). However, I will be creating more general / free content on TH-cam as well as long as this channel continues to grow and be supported.
Sir kindly upload 😊 basic to advanced video in bi 😊 really helpful
I'm glad these videos have been helpful! Feel free to check my course if you're looking for a complete program on Power BI UX/UI Design! --> courses.nextlevelreports.com/order-form-14-days-to-mastering-power-bi-ux-ui-design-website-path
❤
Superb explanation...
Thank you 👍