Understanding time intelligence with Power BI Dan Edwards

Understanding time intelligence with Power BI Dan Edwards, CPA Senior Manager – Crowe LLP Power. BIWorld. Tour. com #PBIWorld. Tour

Meet your presenter • Dan Edwards, CPA – Senior Manager @ Crowe Horwath • Over 25 years implementing and supporting technology solutions • Specializes in financial and operational reporting solutions • Experienced with many ERP, Reporting and BI tools Power. BIWorld. Tour. com #PBIWorld. Tour

Objectives • Develop a better understanding of the time intelligence functions in DAX • Creating and working with a date dimension • Working with relative time • Slicing and filtering by time Power. BIWorld. Tour. com #PBIWorld. Tour

Agenda • What is time intelligence? • Creating and using a date dimension • Relative time • Aggregating over time • Rolling period reporting • Working with date slicers • Summary Power. BIWorld. Tour. com #PBIWorld. Tour

What is time intelligence? Power. BIWorld. Tour. com #PBIWorld. Tour

What is time intelligence? • Doing BI calculations over periods of time or over dates. • • Year to date Month to date Last 12 months …… Power. BIWorld. Tour. com #PBIWorld. Tour

Working with a date dimension Power. BIWorld. Tour. com #PBIWorld. Tour

Auto Date & Time • Automatically creates a hidden date table for each field in the model with a date or date/time data type • Enabled by default • Allows slicing by Year, Quarter, Month and Day • Not supported in Direct Query Power. BIWorld. Tour. com #PBIWorld. Tour

Date Hierarchy • New feature to show the automatic date hierarchy Power. BIWorld. Tour. com #PBIWorld. Tour

Demonstration Auto Date & Time Power. BIWorld. Tour. com #PBIWorld. Tour

Problems with Auto Date and Time • Only works with standard calendar (Jan to Dec) • Needs a datetime column in the fact table • Does not work with multiple fact tables • No time intelligence calculations (YTD, MTD, etc. ) Power. BIWorld. Tour. com #PBIWorld. Tour

Creating a Date Table Power. BIWorld. Tour. com #PBIWorld. Tour

Overcoming Auto Date issues • By adding a date table to the data model Power. BIWorld. Tour. com #PBIWorld. Tour

Date Table • Used for Time Intelligence calculations • Must contain a row for every date in the date range of the data • Often includes additional info like week number, workday, holiday, etc. Power. BIWorld. Tour. com #PBIWorld. Tour

Create a basic date table – creates a record in a table for every year that has data – creates a record in a table for every day in the range from the sales table Power. BIWorld. Tour. com #PBIWorld. Tour

Column Title Formula Example Comments Year([Date]) 2016 Returns four digit year value – set data type to text Short Year VALUE(Right(Year([Date]), 2)) 16 Isolates the year as a two digit number Month Number MONTH([Date]) 1 Isolates the number of the month in the year as one or two digits – hide in report view Month Number Full FORMAT([Date], "MM") 01 Isolates the number of the month in the year as two digits with a leading zero for the first nine months Month Full FORMAT([Date], “MMMM”) January Displays the full name of the month Month Abbr FORMAT([Date], “MMM”) Jan Displays the name of the month as a three letter abbreviation Week Number WEEKNUM([Date) 1 Shows the number of the week in the year – hide in report view Week Number Full FORMAT(Weeknum([Date[), ” 00”) 01 Shows the number of the week in the year with a leading zero for the first nine weeks Day Of Month DAY([Date}]) 1 Displays the number of the day of the month Day Of Month Full FORMAT(Day([Date]), ” 00”) 01 Displays the number of the day of the month with a leading zero for the first nine days Day of Week WEEKDAY([Date]) 1 to 7 Displays the number of the day of the week Day of Week Full FORMAT([Date], ”dddd”) Friday Displays the name of the weekday Day of Week Abbr FORMAT([Date], ”ddd”) Fri Displays the name of the weekday as a three letter abbreviation ISO Date [Year] & [Month Number Full] & [Day of Month Full] 20160101 Displays the date in the ISO (internationally recognized) format of YYYMMDD Full Date [Day of Month] & “ “ & [Month Full] & “ “ & [Year] 1 January 2016 Displays the full date with spaces Quarter Full “Quarter” & ROUNDDOWN(MONTH([Date])/4, 0)+1 Quarter 1 Displays the current quarter Quarter Abbr “Qtr” & ROUNDDOWN(MONTH([Date])/4, 0)+1 Qtr 1 Displays the current quarter as a three letter abbreviation plus the quarter number Quarter “Q” & ROUNDDOWN(MONTH([Date])/4, 0)+1 Q 1 Displays the current quarter in short form Quarter Number FORMAT(ROUNDDOWN(MONTH([Date])/4, 0)+1, ” 00”) 01 Displays the number of current quarter. This is essentially used as a sort column – hide in report view Quarter and Year Date[Quarter] & “ “ & [Year] Q 1 2016 Shows the quarter and the year Month and Year Abbr Date[Month Abbr] & “ “ & [Year] Jan 2016 Shows the abbreviated month and year Quarter and Year Number Date[Year] & [Quarter Number] 201601 Shows the year and quarter numbers. This is essentially used as a sort by column -– hide in report view Year and Week VALUE(‘Date’[Year] & ‘Date’[Week Number Full]) 201601 Indicates the year and week. The VALUE() function ensures that the figure is considered as number by Power BI Desktop Year and Month Number VALUE(‘Date’[Year] & ‘Date’[Month Number Full]) 201601 A numeric value for the year and month – hide in report view Is Work Day IF(OR(‘Date’[Day of Week] = 1, ‘Date’[Day of Week] = 7), “NO”, ”YES”) Yes Returns Yes for Monday thru Friday Power. BIWorld. Tour. com #PBIWorld. Tour

Date Script DAX. txt Edit this line to change the date range • Choose Modeling – New Table • Clip code into the new table formula https: //tinyurl. com/Dan. Date. Script Power. BIWorld. Tour. com Date = ADDCOLUMNS( CALENDAR(Date(2015, 01), (Date(2020, 12, 31))), "Year" , YEAR([Date]), "Short Year" , VALUE(Right(YEAR([Date]), 2)), "Month Number" , Month([Date]), "Month Number Full" , FORMAT([Date], "MM"), "Month Full" , FORMAT([Date], "MMMM"), "Month Abbr" , FORMAT ([Date], "MMM"), "Week Number", WEEKNUM([Date]), "Week Number Full" , Format(WEEKNUM([Date]), "00"), "Day of Month" , DAY([Date]), "Day of Month Full" , FORMAT(Day([Date]), "00"), "Day of Week" , WEEKDAY([Date]), "Day of Week Full" , FORMAT([Date], "dddd"), "Day of Week Abbr" , FORMAT([Date], "ddd"), "ISO Date" , YEAR([Date])&FORMAT(Day([Date]), "00"), "Full Date" , DAY([Date])&" "&FORMAT([Date], "MMMM")&" "&YEAR([Date]), "Quarter Full" , "Quarter " & ROUNDDOWN(Month([Date])/4, 0)+1, "Quarter Abbr" , "Qtr " & ROUNDDOWN(Month([Date])/4, 0)+1, "Quarter" , "Q" & ROUNDDOWN(Month([Date])/4, 0)+1, "Quarter Number" , Format(ROUNDDOWN(Month([Date])/4, 0)+1, "00"), "Quarter and Year" , "Q" & ROUNDDOWN(Month([Date])/4, 0)+1 & " "& YEAR([Date]), "Month and Year Abbr" , FORMAT ([Date], "MMM") & " " & YEAR([Date]), "Quarter and Year Number" , YEAR([Date])&Format(ROUNDDOWN(Month([Date])/4, 0)+1, "00"), "Year and Week", VALUE(YEAR([Date])&FORMAT(WEEKNUM([Date]), 00)), "Year and Month Number", VALUE((YEAR([Date])&FORMAT([Date], "MM"))), "IS Work Day" , IF(OR(WEEKDAY([Date])=1, WEEKDAY([Date])=7), "NO", "YES"), "Current Month", IF(DATEDIFF(TODAY(), [Date], MONTH)=0, "Yes", "No"), "Current Year", IF(DATEDIFF(TODAY(), [Date], Year)=0, "Yes", "No"), "Month Offset", (DATEDIFF(TODAY(), [Date], MONTH)), "Day Offset", (DATEDIFF(TODAY(), [Date], DAY) )) #PBIWorld. Tour

Create a basic date table with M Let Start. Date = #date(2000, 1, 1), End. Date = #date(2020, 12, 31), Fiscal. Month = 7, Number. Of. Days = Duration. Days( End. Date - Start. Date ), Dates = List. Dates(Start. Date, Number. Of. Days+1, #duration(1, 0, 0, 0)), #"Converted to Table" = Table. From. List(Dates, Splitter. Split. By. Nothing(), null, Extra. Values. Error), #"Renamed Columns" = Table. Rename. Columns(#"Converted to Table", {{"Column 1", "Full. Date. Alternate. Key"}}), In #"Renamed Columns" Power. BIWorld. Tour. com #PBIWorld. Tour

Edit these lines to change the date range and fiscal year Date Script M. txt let Start. Date = #date(2000, 1, 1), End. Date = #date(2020, 12, 31), Fiscal. Month = 7, Number. Of. Days = Duration. Days( End. Date - Start. Date ), Dates = List. Dates(Start. Date, Number. Of. Days+1, #duration(1, 0, 0, 0)), #"Converted to Table" = Table. From. List(Dates, Splitter. Split. By. Nothing(), null, Extra. Values. Error), #"Renamed Columns" = Table. Rename. Columns(#"Converted to Table", {{"Column 1", "Full. Date. Alternate. Key"}}), #"Changed Type" = Table. Transform. Column. Types(#"Renamed Columns", {{"Full. Date. Alternate. Key", type date}}), #"Inserted Year" = Table. Add. Column(#"Changed Type", "Year", each Date. Year([Full. Date. Alternate. Key]), type number), #"Change Year to Text" = Table. Transform. Column. Types(#"Inserted Year", {{"Year", type text}}), #"Inserted Short Year" = Table. Add. Column(#"Change Year to Text", "Short Year", each Text. End([Year], 2)), #"Inserted Month" = Table. Add. Column(#"Inserted Short Year", "Month", each Date. Month([Full. Date. Alternate. Key]), type number), #"Inserted Full Month" = Table. Add. Column(#"Inserted Month", "Full Month", each if [Month]< 10 then Text. Combine({"0", Text. From([Month], "en-US")}) else Text. From([Month], "en-US")), #"Inserted Month Name" = Table. Add. Column(#"Inserted Full Month", "Month Name", each Date. Month. Name([Full. Date. Alternate. Key]), type text), #"Inserted Month Name Short" = Table. Add. Column(#"Inserted Month Name", "Short Month", each Text. Start([Month Name], 3)), #"Inserted Quarter #" = Table. Add. Column(#"Inserted Month Name Short", "Quarter #", each Date. Quarter. Of. Year([Full. Date. Alternate. Key]), type number), #"Inserted Quarter" = Table. Add. Column(#"Inserted Quarter #", "Quarter", each Text. Combine({"Qtr ", …. …. … #"Changed Type 3" = Table. Transform. Column. Types(#"Removed Columns", {{"Year", type text}, {"Week of Month", type text}, {"Day of Week", type text}}) in #"Changed Type 3" • Choose Get Data – Blank Query • Clip code into the Advanced Editor https: //tinyurl. com/Dan. MDate. Script Power. BIWorld. Tour. com #PBIWorld. Tour

Handling Fiscal Years • Set a variable for First Fiscal Month • Use following logic in DAX or M • Fiscal Year = if (First Fiscal Month <= Month) then (Year +1) else (Year) • Fiscal Month # = if(First Fiscal Month <= Month) then (Month – First Fiscal Month + 1) else ((12 – First Fiscal Month + 1)+Month) Power. BIWorld. Tour. com #PBIWorld. Tour

Sample Code for Fiscal Years Using DAX • "Fiscal Year", IF(Fiscal. Start <= FORMAT([Date], "MM"), "FY"&(YEAR([Date])+1), "FY"&YEAR([Date])) • "Fiscal Month", Value(IF(Fiscal. Start <= FORMAT([Date], "MM"), (Month([Date]) -Fiscal. Start+1), ((12 Fiscal. Start+1)+Month([Date])))) • "Fiscal Quarter", If (Value(IF(Fiscal. Start <= FORMAT([Date], "MM"), (Month([Date]) -Fiscal. Start+1), ((12 Fiscal. Start+1)+Month([Date])))) < 4, "Q 1", If (Value(IF(Fiscal. Start <= FORMAT([Date], "MM"), (Month([Date]) Fiscal. Start+1), ((12 -Fiscal. Start+1)+Month([Date])))) < 7, "Q 2", If (Value(IF(Fiscal. Start <= FORMAT([Date], "MM"), (Month([Date]) -Fiscal. Start+1), ((12 -Fiscal. Start+1)+Month([Date])))) < 10, "Q 3", "Q 4"))) Power. BIWorld. Tour. com #PBIWorld. Tour

Sample Code for Fiscal Years Using M • "Fiscal Month", each if(Fiscal. Month <= [Month]) then [Month] - Fiscal. Month +1 else (12 Fiscal. Month+1)+[Month]), • "Full Fiscal Month", each if [Fiscal Month]< 10 then Text. Combine({"0", Text. From([Fiscal Month], "en-US")}) else Text. From([Fiscal Month], "en-US")), • "Fiscal Year", each if (Fiscal. Month <= [Month]) then [Year]+1 else [Year]), • "Day of Fiscal Year", each Duration. Days([Full. Date. Alternate. Key]-[Beginning Fiscal Year])), • "Week of Fiscal Year", each Number. Round([Day of Fiscal Year]/7, 0, 0)), • "Quarter of Fiscal Year", each Number. Round(([Fiscal Month]+1)/3, 0, 0)), Power. BIWorld. Tour. com #PBIWorld. Tour

Adding Sort By Columns to Date Table • This is necessary to insure visualizations that contain date elements appear in the correct order Power. BIWorld. Tour. com #PBIWorld. Tour

Sort by Columns for Date Table Power. BIWorld. Tour. com #PBIWorld. Tour

Demonstration Creating a date table Power. BIWorld. Tour. com #PBIWorld. Tour

Add Date Table to Data Model Power. BIWorld. Tour. com #PBIWorld. Tour

Adding the Date Table to the Data Model • Click on Relationship View Icon • Manage Relationships • Click New button • At top – Select Date table from list • Click Date column to select • Bottom half – Select Sales (or other table) • Click Invoice. Date column (other date column) Power. BIWorld. Tour. com #PBIWorld. Tour

Power. BIWorld. Tour. com #PBIWorld. Tour

Without a Date Table With a Date Table Power. BIWorld. Tour. com #PBIWorld. Tour

Mark as Date Table • You can specify the date table in Power BI • You control date hierarchies – disable Power BI date hierarchies Power. BIWorld. Tour. com #PBIWorld. Tour

Error when Date Table is not marked No Error after table is marked Power. BIWorld. Tour. com #PBIWorld. Tour

From Ribbon Bar From Field List – right click Enter date key Power. BIWorld. Tour. com #PBIWorld. Tour Date Field must: • Contain unique values • Contain no null values • Contain contiguous date values from beginning to end • Consistent Date/Time data type

Demonstration Adding date table to model Power. BIWorld. Tour. com #PBIWorld. Tour

Multiple Dates Power. BIWorld. Tour. com #PBIWorld. Tour

Multiple Dates • Date is often a role dimension • Many roles for a date • Many date tables • How many date tables do you need? • Try to use only one table • Use many, only if needed by the model • Many date tables lead to confusion • And issues when slicing • Use proper naming convention Power. BIWorld. Tour. com #PBIWorld. Tour

USERELATIONSHIP Example Power. BIWorld. Tour. com #PBIWorld. Tour

Create a Role Playing Dimension (new table) It is necessary to hide columns and set sorting again Power. BIWorld. Tour. com #PBIWorld. Tour

Demonstration Working with Multiple Dates Power. BIWorld. Tour. com #PBIWorld. Tour

SUMMARY: Creating and Using a Date Dimension • Use a separate date table/dimension • Date tables are easy to create • USERELATIONSHIP allows you to avoid multiple date tables Power. BIWorld. Tour. com #PBIWorld. Tour

Relative Time Power. BIWorld. Tour. com #PBIWorld. Tour

Relative Date Filtering • Allows you to focus reports on a specific time frame relative to the current date • Is in the last • Is in this • Is in the next Power. BIWorld. Tour. com • • Days Weeks Calendar Weeks Months Calendar Months Years Calendar Years #PBIWorld. Tour

Power. BIWorld. Tour. com #PBIWorld. Tour

Relative Date Slicer Power. BIWorld. Tour. com #PBIWorld. Tour

Demonstration Relative Time Power. BIWorld. Tour. com #PBIWorld. Tour

SUMMARY: Relative Time • Relative Date Filtering allows you to filter a specific visualization or report based on today date • Bookmarks can be used to help user interaction • When use Date as a slicer it can be set to relative Power. BIWorld. Tour. com #PBIWorld. Tour

Aggregating over time Power. BIWorld. Tour. com #PBIWorld. Tour

Power. BIWorld. Tour. com #PBIWorld. Tour

Aggregations Over Time • Many useful aggregations • YTD – Year to Date • QTD – Quarter to Date • MTD – Month to Date • Requires a DATE table • Need to understand CALCULATE (DAX) Power. BIWorld. Tour. com #PBIWorld. Tour

Time Intelligence is just a filter How Time Intelligence Functions Work 1. “Detect” the current filter context to determine what the “current” selected period is 2. Remove the calendar filtering from the underlying tables so that data from “all time” is once again available 3. Perform a time shift to find a different period in time (as specified in the formula) 4. Reapply filters on the data model for the time shifted period prior to calculating the final value Power. BIWorld. Tour. com #PBIWorld. Tour

Custom vs. Inbuilt Time Intelligence Power. BIWorld. Tour. com #PBIWorld. Tour

Example using custom formula Power. BIWorld. Tour. com #PBIWorld. Tour

How to read custom time intelligence formula 1 2 3 4 5 6 7 8 9 Power. BIWorld. Tour. com #PBIWorld. Tour

Formula Overview Line 2 (CALCULATE) is the only function that can change the natural filtering behavior coming from a visual – that’s what it does. CALCULATE always filters first, evaluates second. So lines 5 – 8 are executed first (filters applied) and then the formula on line 3 is evaluated last. Lines 4 – 8 (FILTER) is where the filtering occurs. There a few confusing things here. Line 5 refers to ALL(‘Date’) instead of simply Date. Lines 6 and 7 seem to be evaluating against themselves – very confusing. Line 7 starts with a double ampersand && Power. BIWorld. Tour. com #PBIWorld. Tour

Line 5 ALL (‘Date’) • Filter portion of CALCULATE always operates in the current filter context • The purpose of the ALL Function is to remove all filters from the current filter context • If it didn’t remove these filters, it would not be possible to access dates outside of the specific month • Now that all of the filters are removed, it is time to add back the date filters that are needed Power. BIWorld. Tour. com #PBIWorld. Tour

Line 6 ‘Date’[Year]=Max(‘Date’[Year]) • Whenever you see an aggregation function (in this case MAX) around a column, it means “go and read the value from the current filter context”. • Whenever you see a “naked” reference to a column (i. e. there is no aggregation function around the column), then it means “go and apply a new filter on this column in the data model”. • Using these 2 rules, it should be clear that MAX(‘Date’[Year]) in the current filter context is = 2018. Line 6 says “Go and apply a new filter on ‘Date’[Year] to be equal to 2018 • Note that you can use any aggregation function in these formulas that does the job. In line 6, you could use SUM, MIN, MAX, AVERAGE or any other similar aggregation function and still get the same result. You could also use VALUES or DISTINCT in the case of line 6, and LASTDATE in the case of line 7. There is no right or wrong answer, simply think about the need and then find the best function to use. Power. BIWorld. Tour. com #PBIWorld. Tour

Line 7 &&‘Date’[Date]<=Max(‘Date’[Date]) • The double ampersand && is the inline syntax for a logical AND • Following the same logic as with line 6, the way to read line 7 is as follows: • “AND also go and apply another new filter this time on ‘Date’[Date] to be less than or equal to 31, July 2018” • With both of these filters applied in a logical AND, the Calendar table will contain all of the dates from 1 Jan 2018 until 31 July 2018 Power. BIWorld. Tour. com #PBIWorld. Tour

Power. BIWorld. Tour. com #PBIWorld. Tour

Sales Amount Last Month (option 1 & 2) PARALLELPERIOD DATEADD Power. BIWorld. Tour. com #PBIWorld. Tour

Sales Amount Last Month (option 3) Power. BIWorld. Tour. com #PBIWorld. Tour

Sales Amount Last Year SAMEPERIODLASTYEAR DATEADD Power. BIWorld. Tour. com #PBIWorld. Tour

Sales Amount YTD (option 1 & 2) DATESBETWEEN Power. BIWorld. Tour. com #PBIWorld. Tour

Sales Amount YTD (option 3) Power. BIWorld. Tour. com #PBIWorld. Tour

Sales YTD Last Year SAMEPERIODLASTYEAR DATEADD Power. BIWorld. Tour. com #PBIWorld. Tour

Sales Amount Life to Date Power. BIWorld. Tour. com #PBIWorld. Tour

Last 12 Months Rolling Sales DATESINPERIOD LASTDATE Power. BIWorld. Tour. com #PBIWorld. Tour

Using Inbuilt Time Intelligence Functions Inbuilt time intelligence functions are easier to write as they are designed to protect the user from the complexity Power. BIWorld. Tour. com #PBIWorld. Tour

Power BI: DAX: Time Intelligence Functions • CLOSINGBALANCEMONTH • NEXTQUARTER • CLOSINGBALANCEQUARTER • NEXTYEAR • CLOSINGBALANCEYEAR • OPENINGBALANCEMONTH • DATEADD • OPENINGBALANCEQUARTER • DATESBETWEEN • OPENINGBALANCEYEAR • DATESINPERIOD • PARALLELPERIOD • DATESMTD • PREVIOUSDAY • DATESQTD • PREVIOUSMONTH • DATESYTD • PREVIOUSQUARTER • ENDOFMONTH • PREVIOUSYEAR • ENDOFQUARTER • SAMEPERIODLASTYEAR • ENDOFYEAR • STARTOFMONTH • FIRSTDATE • STARTOFQUARTER • FIRSTNONBLANK • STARTOFYEAR • LASTDATE • TOTALMTD • LASTNONBLANK • TOTALQTD • NEXTDAY • TOTALYTD • NEXTMONTH https: //msdn. microsoft. com/en-us/library/ee 634763. aspx Power. BIWorld. Tour. com #PBIWorld. Tour

Another Option: Quick Measures Power. BIWorld. Tour. com #PBIWorld. Tour

Using Quick Measures for Time Intelligence • Year to date • Quarter to date • Month to date • Year over year change • Quarter over quarter change • Month over month change • Rolling average Power. BIWorld. Tour. com #PBIWorld. Tour

Quick Measures • Right click on measure to begin Power. BIWorld. Tour. com #PBIWorld. Tour

Demonstration Quick Measures Power. BIWorld. Tour. com #PBIWorld. Tour

SUMMARY: Aggregating over time • DAX includes many useful functions • Time Intelligence is simply a filter • Quick Measures are a great way to start • Time Intelligence functions require a Date Dimension Power. BIWorld. Tour. com #PBIWorld. Tour

Rolling period reporting Power. BIWorld. Tour. com #PBIWorld. Tour

Rolling period reporting • Many users like to see sales trends • For example • LAST 12 MONTHS Power. BIWorld. Tour. com #PBIWorld. Tour

Rolling period reporting problem • Adding a date slicer limits the points on the line chart This happens because the date dimension relates to the sales fact table and it filters the data for the selected month Power. BIWorld. Tour. com #PBIWorld. Tour

Configuring the data model for user selected rolling 12 month reporting • Step 1 – Create two tables, Table 1 = Year & Table 2 = Month (no relationships to other tables) • Step 2 – Create two new measures: Current Month Sales & Same Month Last Year Sales • Step 3 – Create a chart using the two new measures with axis from the date dimension. Add slicers from Year & Month tables in Step 1 Power. BIWorld. Tour. com #PBIWorld. Tour

Configuring the data model for user selected rolling 12 month reporting Power. BIWorld. Tour. com #PBIWorld. Tour

Current Month Sales = Power. BIWorld. Tour. com #PBIWorld. Tour

Same Month Last Year Sales = Power. BIWorld. Tour. com #PBIWorld. Tour

Configuring the data model for user selected rolling 12 month reporting Power. BIWorld. Tour. com #PBIWorld. Tour

Configuring the data model for user selected rolling 12 month reporting From the related date table Custom time measures created for this report Power. BIWorld. Tour. com #PBIWorld. Tour

Demonstration Rolling 12 Month Report Power. BIWorld. Tour. com #PBIWorld. Tour

SUMMARY: Rolling 12 Month Report • This allows users to select the date for the report • Formula can easily be adjusted for other time frames (3 months, 6 months, etc. ) • Slicers are from unrelated Year & Month tables Power. BIWorld. Tour. com #PBIWorld. Tour

Working with date slicers Power. BIWorld. Tour. com #PBIWorld. Tour

Working with date slicers • Many options available if you use date field • Also can use months, quarters, years like other dimensions Power. BIWorld. Tour. com #PBIWorld. Tour

Hierarchy Slicer • A custom visual that allows slicing with a hierarchy Power. BIWorld. Tour. com #PBIWorld. Tour

Demonstration Date Slicers Power. BIWorld. Tour. com #PBIWorld. Tour

Sync Slicers • Add the slicer once and sync it to the appropriate pages • Use the Sync Panel • Two options • Applied • Visible Power. BIWorld. Tour. com #PBIWorld. Tour

Sync Slicers Sync Visible Power. BIWorld. Tour. com #PBIWorld. Tour

Demonstration Sync Slicers Power. BIWorld. Tour. com #PBIWorld. Tour

SUMMARY: Working with date slicers • There a lot of options for slicers with dates • There are custom visuals available • Date based slicers may effect your time aggregations Power. BIWorld. Tour. com #PBIWorld. Tour

Summary Power. BIWorld. Tour. com #PBIWorld. Tour

SUMMARY: Understanding time intelligence • There a lot of options and capabilities with dates in Power BI • It is highly recommended to create a date table • There is more than one way to create a measure • Time Intelligence requires some understanding of DAX Power. BIWorld. Tour. com #PBIWorld. Tour

Questions Power. BIWorld. Tour. com #PBIWorld. Tour

THANK YOU Dan Edwards, CPA Crowe LLP Direct: 714 -668 -5348 Cell: 714 -231 -2202 dan. edwards@crowe. com @dsecpa https: //www. linkedin. com/in/dsedwards/ Power. BIWorld. Tour. com #PBIWorld. Tour

Don’t forget to join your local PUG to enjoy year-round networking and learning.