DAX for Power BI Part 6.1 - Introduction to Time Intelligence Functions

Подписаться 208 тыс.

Просмотров 6 тыс.

50% 1

By Andrew Gould
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can join this channel to get access to perks:
/ @wiseowltutorials
Or you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
Download files here www.wiseowl.co.uk/power-bi/vi...
This video shows you how to get started with time intelligence functions in DAX. You'll learn how to make Power BI create a date table for each date column in your data model automatically. You'll also learn what the auto date table contains and why it's important for the operation of the time intelligence functions. You'll see how to display different levels of a date hierarchy and how to use the DateAdd function to modify the filter context applied to a measure to compare a calculation with past and future dates. Finally, you'll learn how to use the SamePeriodLastYear function as a shorthand for a specific use of the DateAdd function.
Chapters
00:00 Topic list
00:50 The Auto Date Time Setting
02:53 What the Auto Date Table Contains
05:55 Grouping a Measure by Years
07:28 The DateAdd Function
11:18 Showing Different Levels of the Hierarchy
14:02 Comparing to the Previous Year
14:50 Excluding Blanks
16:48 Comparing with the Previous Quarter
18:32 Using the IsFiltered Function
23:41 Comparing the Previous Month Using Variables
29:05 Using a Specialised Time Intelligence Function
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Power BI, DAX, VBA, Python, Visual C#, Microsoft SQL Server, SQL Server Reporting Services SSRS, SQL Server Integration Services SSIS, Microsoft Access and more!

Опубликовано:

5 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 16

@pon00050 7 месяцев назад

Thank you for sharing your knowledge. For the time being, I am spending time on other endeavors, but I look forward to the day that I will be going through these tutorial videos.

@WiseOwlTutorials 7 месяцев назад

You're welcome! Thanks for taking the time to leave a comment and I hope you find time to start watching soon!

@kiendra 7 месяцев назад

Yoooo wise owl got the platinum skin 🤯🤯😳😳😳🔥🔥🔥🔥

@WiseOwlTutorials 7 месяцев назад

Glad you like it 😀

@hadibq 2 года назад

Nice precise examples of using the dates in filters!! Thanks! very well done Andrew! a suggestion please: would it be possible to have a link in the description to the full playlist, so one can easily go to the playlist from a randomly found video. yt doesn't handle this yet, guess it's a good feature to add :)

@WiseOwlTutorials 2 года назад

Thanks Hadi! And thanks for the suggestion about the playlist link. We do already include that link in the end screen of the video but if it will help to have it in the description as well I'll make a note to do this for future videos!

@bretaddison4146 7 месяцев назад

I find the column totals confusing on previous quarter and previous year. How should they be understood ?

@WiseOwlTutorials 7 месяцев назад

Hi Bret! Using the DateAdd function shifts the date range of the current filter context by the specified amount. So, the date range of the filter context for the Total row spans 1 Jan 2010 to 31 Dec 2016. If you shift that date range back 1 year it becomes 1 Jan 2009 to 31 Dec 2015 so you get the average of all Run Times between those dates. If you shift the date range back 1 quarter it becomes 1 Oct 2009 to 30 Sep 2016 so you get the average of all Run Time between those dates. They're not particularly useful values to see in this matrix, I agree! It would be better to suppress them using a function like ISINSCOPE or HASONEVALUE to only show subtotals for the relevant groups. I hope that helps!

@ArchStanton806 6 месяцев назад

Hi Andy, I have a Matrix that shows various Time Intelligence based figures and one of them is the change in volume of Cases Month on Month as a %. Cases MoM% is a Measure (see below) that is , as it suggests, the variance as a % on the previous month. How do I stop the Variance showing on Year sub-total level? Is there anything that can be changed in the code below that would just show the % next to the months and not the Year? Cases MoM% = VAR ActiveCase = [YTD Active Cases] VAR ActiveCasesPM = [Previous Mth] VAR Result = IF( [YTD Active Cases] 0 && [YTD Active Cases PY] 0, DIVIDE(ActiveCase - ActiveCasesPM, ActiveCasesPM) ) RETURN Result I've been looking for a solution everywhere for this and the easy way out is to change the colour of the font and essentially 'blank' the value out. I want to know how to do this through DAX. Fingers crossed its straightforward!

@WiseOwlTutorials 6 месяцев назад

Hi Andy! Yeah, there are a couple of fairly straightforward things you can do here. Try something like: RETURN IF( ISINSCOPE('Your month field name'), Result ) I hope it helps!

@ArchStanton806 6 месяцев назад

Thanks, that works! Much appreciated Andy

@WiseOwlTutorials 6 месяцев назад

@@ArchStanton806 No probs!

@vincentlehmann646 7 месяцев назад

what does DAX stand for ?

@WiseOwlTutorials 7 месяцев назад

Hi! Its Data Analysis eXpressions.

@pabeader1941 7 месяцев назад

Too bad that it isn't really Time intelligence. Should have been called Period Intelligence or maybe Date Intelligence. I wish someone would figure out haw to actually do Time Intelligence. Things like same period last hour, or Hour over Hour. That would be Time Intelligence.

@WiseOwlTutorials 7 месяцев назад

Hi! Time Intelligence is Microsoft's official name for these features and functions learn.microsoft.com/en-us/dax/time-intelligence-functions-dax learn.microsoft.com/en-us/training/modules/dax-power-bi-time-intelligence/