At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
This is the final result of part four, the Time Intelligence Dashboard. We are comparing sales with last year, calculating year-to-date sales, creating a running total, and viewing the category ranking over the years. Let's start. Hello! This is part four of the Power BI Wave X Dashboard series. This time, we are learning time intelligence, which is DAX functions for time-based analysis such as year-to-date, last year, and running total.
Step 1: Duplicate the Sales2 page into Time Intelligence
Duplicate the sales 2 page, rename it to time intelligence, then change its page title.
Step 2: Create the Sales YTD measure using TOTALYTD
The first measure is sales YTD. Right-click the sales table, select new measure, then type totalit with the content total sales and the date column. The date column in this data has 7 spaces at the end of its name, and we use the date column at the end, which is the date column from the automatically generated hierarchy with a complete order. Format as currency without decimals. The second measure is sales Lee or sales last year.
Step 3: Create the Sales LY measure using SAMEPERIODLASTYEAR
Use calculate with total sales, then filter until the last year period in the same date column. This function shifts the period exactly one year back. If using a regular date column that is not ordered, this function will result in an error.
Step 4: Create the Running Total measure
The third measure is the running total, which is cumulative sales. Calculate total sales with the filter. Take all selected dates with alselected, then keep only the dates that are less than or equal to the last date in the current context. Select the KPI card.
Step 5: YTD and Last Year KPI Cards
Remove total quantity, total profit, and average profit per unit, then drag sales YTD and sales Lee to the value column. On the sales line chart, drag sales Lee to the y-axis as the second line.
Step 6: Sales compared to last year
Now, sales for each year can be directly compared with the previous year. The title becomes sales versus last year.
Step 7: Running Total Area Chart
We change the sales growth chart to an area chart, then replace the y-axis with the running total. The area chart shows the cumulative sales that keep increasing every quarter. The title becomes running total sales.
Step 8: Ribbon Chart for category ranking per year
We change our top distributor chart to a ribbon chart. Fill the x-axis with date, y-axis with sales, and legend with category type. These ribbons show the changing category rankings each year. The title becomes category ranking by year.
Step 9: Sales YTD by category
Finally, we replace the sales by category chart values with sales YTD, so it displays current year sales per category. The title becomes sales YTD by category.
Done! Time Intelligence Dashboard
Done. The time intelligence dashboard is now complete with 3 time measures that can be reused in other reports. Save with Ctrl + S. See you in part five about payment analysis. Want to have a Power BI dashboard like this? Cerdas Inovasi Digital offers Power BI courses, dashboard creation services and report automation, as well as selling Power BI project files from this tutorial series. Contact us via WhatsApp, or visit cerdasinovasidigital.com.
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
52 video classes1,276 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
