Refresh Date/ Time: Auto swich between BST & GMT
A table that uses DateTime.LocalNow() to dynamically change the time from BST to GMT and vice versa. createOrReplace table Refresh_DateTime column RefreshDate dataType: dateTime formatString: General Date summarizeBy: none sourceColumn: RefreshDate annotation SummarizationSetBy = Automatic column Local_Date_Time dataType: dateTime formatString: dd/mm/yy hh:nn summarizeBy: none sourceColumn: Local_Date_Time annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isCustom":true} partition Refresh_DateTime = m mode: import source = ``` let Source = #table(type table[RefreshDate=datetime], {{DateTime.LocalNow()}}), Test = #table(type table[RefreshDate=datetime], {{#datetime(2026,10,25,11,0,0)}}), // To test, change the "Source" reference to "Test" AddBST = Table.AddColumn(Source, "Local_Date_Time", each let dt = [RefreshDate], year = Date.Year(dt), dstStart = Date.StartOfWeek(#date(year, 3, 31), Day.Sunday), dstEnd = Date.StartOfWeek(#date(year, 10, 31), Day.Sunday), isDST = Date.From(dt) >= dstStart and Date.From(dt) < dstEnd, offset = if isDST then 1 else 0, bstTime = dt + #duration(0, offset, 0, 0) in bstTime, type datetime ) in AddBST ``` annotation PBI_NavigationStepName = Navigation annotation PBI_ResultType = Table
Calendar table
PowerQuery implementation of a calendar table. createOrReplace /// Calendar table table Calendar column Day dataType: int64 formatString: 0 summarizeBy: none sourceColumn: Day column Month dataType: int64 formatString: 0 summarizeBy: none sourceColumn: Month column Quarter dataType: string summarizeBy: none sourceColumn: Quarter column Year dataType: int64 formatString: 0 summarizeBy: none sourceColumn: Year column Date dataType: dateTime formatString: Long Date summarizeBy: none sourceColumn: Date annotation UnderlyingDateTimeDataType = Date column 'Month Name' dataType: string summarizeBy: none sourceColumn: Month Name sortByColumn: Month column Year-Month dataType: dateTime formatString: mmm yyyy summarizeBy: none sourceColumn: Year-Month annotation UnderlyingDateTimeDataType = Date annotation PBI_FormatHint = {"isCustom":true} column 'Week Number' dataType: int64 formatString: 0 summarizeBy: none sourceColumn: Week Number column 'Day of Week' dataType: int64 formatString: 0 summarizeBy: none sourceColumn: Day of Week column 'Day Name' dataType: string summarizeBy: none sourceColumn: Day Name sortByColumn: 'Day of Week' column 'Is Weekend' dataType: boolean formatString: """TRUE"";""TRUE"";""FALSE""" summarizeBy: none sourceColumn: Is Weekend column 'Fiscal Year' dataType: int64 formatString: 0 summarizeBy: none sourceColumn: Fiscal Year column 'Fiscal Quarter' dataType: string summarizeBy: none sourceColumn: Fiscal Quarter hierarchy Year-Month-Day level Year column: Year level Month column: Month level Day column: Day partition Calendar = m mode: import source = ``` let P_Today = DateTime.LocalNow(), StartDate = #date(Date.Year(DateTime.LocalNow()) - 3, 1, 1), EndDate = #date(Date.Year(DateTime.LocalNow()), 12, 31), // Generate a list of dates DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)), // Convert list to a table Calendar = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}), // Add columns for different date attributes AddYear = Table.AddColumn(Calendar, "Year", each Date.Year([Date])), AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date])), AddDay = Table.AddColumn(AddMonth, "Day", each Date.Day([Date])), AddMonthName = Table.AddColumn(AddDay, "Month Name", each Date.ToText([Date], "MMM")), AddYearMonth = Table.AddColumn(AddMonthName, "Year-Month", each Text.From(Date.Year([Date])) & " " & Date.ToText([Date], "MMM")), //AddYearMonthKey = Table.AddColumn(AddYearMonth, "Year-Month Key", each Date.Year([Date]) * 100 + Date.Month([Date])), AddQuarter = Table.AddColumn(AddYearMonth, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))), AddWeek = Table.AddColumn(AddQuarter, "Week Number", each Date.WeekOfYear([Date])), AddDayOfWeek = Table.AddColumn(AddWeek, "Day of Week", each Date.DayOfWeek([Date]) + 1), AddDayName = Table.AddColumn(AddDayOfWeek, "Day Name", each Date.ToText([Date], "dddd")), AddIsWeekend = Table.AddColumn(AddDayName, "Is Weekend", each if Date.DayOfWeek([Date]) >= 5 then true else false), // Add fiscal year and period adjustments AddFiscalYear = Table.AddColumn(AddIsWeekend, "Fiscal Year", each if Date.Month([Date]) >= 7 then Date.Year([Date]) + 1 else Date.Year([Date])), AddFiscalQuarter = Table.AddColumn(AddFiscalYear, "Fiscal Quarter", each "FQ" & Text.From(Number.IntegerDivide((Date.Month([Date]) + 5), 3))), #"Changed Type" = Table.TransformColumnTypes(AddFiscalQuarter,{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"Month Name", type text}, {"Year-Month", type date}, {"Quarter", type text}, {"Week Number", Int64.Type}, {"Day of Week", Int64.Type}, {"Day Name", type text}, {"Is Weekend", type logical}, {"Fiscal Year", Int64.Type}, {"Fiscal Quarter", type text}}) in #"Changed Type" ```Create a Multi-Parameter Table for Thematic Filtering in Power BI
This script creates a ‘Multi Parameter’ table in Power BI that allows you to filter on a single parameter and return up to four associated field references (columns or measures). With this approach, you can group related fields into thematic sets, making it easy to synchronize multiple visuals on a report page to a specific topic or analytical perspective. A key consideration is that the order of the fields must be preserved, otherwise Power BI will not correctly recognize the parameter structure. This technique is ideal for creating flexible, topic-based dashboards where multiple visuals respond to a unified filter context. Learn more about adapting field parameters to your project here: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters createOrReplace table 'Multi Parameter' lineageTag: bcdcaa89-6654-44ce-9eb9-0679c7b99e4b column 'Parameter 4' lineageTag: f9032c01-9dfe-4e07-b83a-f531869235a4 summarizeBy: none sourceColumn: [Value8] sortByColumn: 'Parameter Order' relatedColumnDetails groupByColumn: 'Parameter Fields 4' annotation SummarizationSetBy = Automatic column 'Parameter 3' lineageTag: f9032c01-9dfe-4e07-b83a-f531869235a3 summarizeBy: none sourceColumn: [Value6] sortByColumn: 'Parameter Order' relatedColumnDetails groupByColumn: 'Parameter Fields 3' annotation SummarizationSetBy = Automatic column 'Parameter 2' lineageTag: f9032c01-9dfe-4e07-b83a-f531869235a2 summarizeBy: none sourceColumn: [Value4] sortByColumn: 'Parameter Order' relatedColumnDetails groupByColumn: 'Parameter Fields 2' annotation SummarizationSetBy = Automatic column 'Parameter 1' lineageTag: f9032c01-9dfe-4e07-b83a-f531869235a1 summarizeBy: none sourceColumn: [Value1] sortByColumn: 'Parameter Order' relatedColumnDetails groupByColumn: 'Parameter Fields 1' annotation SummarizationSetBy = Automatic column 'Parameter Fields 1' isHidden lineageTag: 61e451b3-f5d5-497e-94cd-3f763efbdd41 summarizeBy: none sourceColumn: [Value2] sortByColumn: 'Parameter Order' extendedProperty ParameterMetadata = {"version":3,"kind":2} annotation SummarizationSetBy = Automatic column 'Parameter Fields 2' isHidden lineageTag: 61e451b3-f5d5-497e-94cd-3f763efbdd42 summarizeBy: none sourceColumn: [Value5] sortByColumn: 'Parameter Order' extendedProperty ParameterMetadata = {"version":3,"kind":2} annotation SummarizationSetBy = Automatic column 'Parameter Fields 3' isHidden lineageTag: 61e451b3-f5d5-497e-94cd-3f763efbdd43 summarizeBy: none sourceColumn: [Value7] sortByColumn: 'Parameter Order' extendedProperty ParameterMetadata = {"version":3,"kind":2} annotation SummarizationSetBy = Automatic column 'Parameter Fields 4' isHidden lineageTag: 61e451b3-f5d5-497e-94cd-3f763efbdd44 summarizeBy: none sourceColumn: [Value9] sortByColumn: 'Parameter Order' extendedProperty ParameterMetadata = {"version":3,"kind":2} annotation SummarizationSetBy = Automatic column 'Parameter Order' isHidden formatString: 0 lineageTag: 8425d3f0-fb91-458d-97c8-b0e3c3acce97 summarizeBy: sum sourceColumn: [Value3] annotation SummarizationSetBy = Automatic column 'Parameter Filter' lineageTag: f9032c01-9dfe-4e07-b83a-f53186923566 summarizeBy: none sourceColumn: [Value10] sortByColumn: 'Parameter Order' annotation SummarizationSetBy = Automatic partition Parameter = calculated mode: import source = { ("Customer", NAMEOF('Customer'[Customer]), 0, "Company", NAMEOF('Customer'[Company]), "Country Name", NAMEOF('Customer'[Country Name]), "Occupation", NAMEOF('Customer'[Occupation]), "Customer"), ("Product", NAMEOF('Product'[Product]), 1, "Category Name", NAMEOF('Product'[Category Name]), "Manufacturer", NAMEOF('Product'[Manufacturer]), "Brand", NAMEOF('Product'[Brand]), "Product") } annotation PBI_Id = 327d5af8295f4794bec0a018ecba78f0
3 "Measure Tables" With Icons
createOrReplace table '🎯Measures | 1.📈KPIs' lineageTag: 5b77fb14-15e7-485f-b4f2-04d64e555d3f column Value isHidden lineageTag: 7e2e1395-3081-47f7-8aad-43ead4d97bc6 isNameInferred sourceColumn: [Value] partition '🎯Measures | 1.📈KPIs' = calculated mode: import source = {0} table '🎯Measures | 2. #⃣ Variables' lineageTag: 136bb655-ea44-4e2c-8e2c-4b1a937d631a column Value isHidden lineageTag: 5394559c-99f0-484f-8e48-e5940b23d215 isNameInferred sourceColumn: [Value] partition '🎯Measures | 2. #⃣ Variables' = calculated mode: import source = {0} table '🎯Measures | 3.📋Titles and Labels' lineageTag: 5d7d76d1-ef56-4229-abe9-7f370a65585b column Value isHidden lineageTag: 453c18cd-ffa2-49be-b59b-d60ace1d4ab4 isNameInferred sourceColumn: [Value] partition '🎯Measures | 3.📋Titles and Labels' = calculated mode: import source = {0}
PQ Function - Calendar Table by Lars Schreiber
This German Power Query Function will create a CalendarTable with a fixed start and end range. Because of characterlimit here is the TMDL script: PBI-Tools/TMDL Repo/PQ Function - Calendar by Lars Schreiber.txt at main · KornAlexander/PBI-Tools See more info from Lars about the topic in his blog here: Meine projekterprobte Kalendertabelle für Power BI und Power Pivot | THE SELF-SERVICE-BI BLOGCalc Table - Calendar
This table is a fairly simple Calc Calendar Table. - With AUTOCALENDAR - With 1 FiscalYear column - With Favorites Folder - Splitting into Display Folders Text and Number Columns createOrReplace table CalcCalendar dataCategory: Time column Date isKey formatString: Short Date displayFolder: 1. Favorites summarizeBy: none isNameInferred sourceColumn: [Date] annotation SummarizationSetBy = Automatic annotation UnderlyingDateTimeDataType = Date column Month formatString: 0 displayFolder: 2. Calendar Date\2. Number Columns summarizeBy: sum isNameInferred sourceColumn: [Month] annotation SummarizationSetBy = Automatic column 'Fiscal Year' formatString: 0 displayFolder: 3. Fiscal Date\2. Numbers;1. Favorites summarizeBy: sum isNameInferred sourceColumn: [Fiscal Year] annotation SummarizationSetBy = Automatic column Year formatString: 0 displayFolder: 2. Calendar Date\2. Number Columns;1. Favorites summarizeBy: sum isNameInferred sourceColumn: [Year] annotation SummarizationSetBy = Automatic column 'Month (MMM)' displayFolder: 2. Calendar Date\3. Text Columns;1. Favorites summarizeBy: none isNameInferred sourceColumn: [Month (MMM)] sortByColumn: Month annotation SummarizationSetBy = Automatic column Day formatString: 0 displayFolder: 2. Calendar Date\2. Number Columns summarizeBy: sum isNameInferred sourceColumn: [Day] annotation SummarizationSetBy = Automatic column 'Is Before This Month' formatString: """TRUE"";""TRUE"";""FALSE""" displayFolder: 4. Flags summarizeBy: none isNameInferred sourceColumn: [Is Before This Month] annotation SummarizationSetBy = Automatic column 'Is Current Fiscal Year' formatString: """TRUE"";""TRUE"";""FALSE""" displayFolder: 4. Flags summarizeBy: none isNameInferred sourceColumn: [Is Current Fiscal Year] annotation SummarizationSetBy = Automatic column 'Is Previous Fiscal Year' formatString: """TRUE"";""TRUE"";""FALSE""" displayFolder: 4. Flags summarizeBy: none isNameInferred sourceColumn: [Is Previous Fiscal Year] annotation SummarizationSetBy = Automatic column 'Is Current Calendar Year' formatString: """TRUE"";""TRUE"";""FALSE""" displayFolder: 4. Flags summarizeBy: none isNameInferred sourceColumn: [Is Current Calendar Year] annotation SummarizationSetBy = Automatic column 'Is Previous Calendar Year' formatString: """TRUE"";""TRUE"";""FALSE""" displayFolder: 4. Flags summarizeBy: none isNameInferred sourceColumn: [Is Previous Calendar Year] annotation SummarizationSetBy = Automatic column 'Is Current Month' formatString: """TRUE"";""TRUE"";""FALSE""" displayFolder: 4. Flags summarizeBy: none isNameInferred sourceColumn: [Is Current Month] annotation SummarizationSetBy = Automatic column 'Is Previous Month' formatString: """TRUE"";""TRUE"";""FALSE""" displayFolder: 4. Flags summarizeBy: none isNameInferred sourceColumn: [Is Previous Month] annotation SummarizationSetBy = Automatic column 'Year Month Key' formatString: 0 displayFolder: 2. Calendar Date\2. Number Columns summarizeBy: count sourceColumn: [Month Key] annotation SummarizationSetBy = Automatic column 'Relative Month' formatString: 0 displayFolder: 4. Flags summarizeBy: sum isNameInferred sourceColumn: [Relative Month] annotation SummarizationSetBy = Automatic column Quarter displayFolder: 2. Calendar Date\3. Text Columns summarizeBy: none isNameInferred sourceColumn: [Quarter] annotation SummarizationSetBy = Automatic column 'End of Month' formatString: General Date displayFolder: 2. Calendar Date\2. Number Columns summarizeBy: none isNameInferred sourceColumn: [End of Month] annotation SummarizationSetBy = Automatic column 'Week of Year' formatString: 0 displayFolder: 2. Calendar Date\2. Number Columns summarizeBy: sum isNameInferred sourceColumn: [Week of Year] annotation SummarizationSetBy = Automatic column Weekday formatString: 0 displayFolder: 2. Calendar Date\2. Number Columns summarizeBy: sum isNameInferred sourceColumn: [Weekday] annotation SummarizationSetBy = Automatic column 'Is Current or Past Months' displayFolder: 4. Flags summarizeBy: none sourceColumn: [Is Current or Past Month] annotation SummarizationSetBy = Automatic hierarchy 'Date Hierarchy' displayFolder: 2. Calendar Date\1. Hierarchy level Year column: Year level Month column: Month level Date column: Date hierarchy 'Fiscal Date Hierarchy' displayFolder: 3. Fiscal Date\1. Hierarchy level 'Fiscal Year' column: 'Fiscal Year' hierarchy 'Calendar Hierarchy' displayFolder: 1. Favorites level Year column: Year level 'Month (MMM)' column: 'Month (MMM)' partition CalcCalendar = calculated mode: import source = ``` VAR Today = TODAY() -- Modify for testing VAR MonthStartFiscalYear = 10 RETURN ADDCOLUMNS( /*CALENDAR( DATE ( 2022, 1, 1 ), DATE ( 2027, 1, 1 )), */ --use it if CALENDARAUTO is not an option CALENDARAUTO(), "Year",YEAR([Date]), "Quarter", "Q "&QUARTER([Date]), "Month", MONTH([Date]), "Month (MMM)", FORMAT([Date], "MMM"), "Day",DAY([Date]), "Fiscal Year", YEAR([Date]) + IF(MONTH([Date]) >= MonthStartFiscalYear, 1, 0), "End of Month", EOMONTH([Date], 0), "Week of Year", WEEKNUM([Date]), "Weekday", WEEKDAY([Date]), "Is Current or Past Month", IF([Date] <= EOMONTH(TODAY(), 0), "Yes", "No"), "Is Before This Month",FORMAT([Date],"YYYYMM")<FORMAT(Today,"YYYYMM"), -- Current/Previous Fiscal Year flags "Is Current Fiscal Year", VAR CurrentFiscalYear = YEAR(Today) + IF(MONTH(Today) >= MonthStartFiscalYear, 1, 0) RETURN YEAR([Date]) + IF(MONTH([Date]) >= MonthStartFiscalYear, 1, 0) = CurrentFiscalYear, "Is Previous Fiscal Year", VAR CurrentFiscalYear = YEAR(Today) + IF(MONTH(Today) >= MonthStartFiscalYear, 1, 0) RETURN YEAR([Date]) + IF(MONTH([Date]) >= MonthStartFiscalYear, 1, 0) = CurrentFiscalYear - 1, -- Current/Previous Calendar Year flags "Is Current Calendar Year", YEAR([Date]) = YEAR(Today), "Is Previous Calendar Year", YEAR([Date]) = YEAR(Today) - 1, -- Current/Previous Month flags "Is Current Month", YEAR([Date]) = YEAR(Today) && MONTH([Date]) = MONTH(Today), "Is Previous Month", VAR PrevMonthYear = IF(MONTH(Today) = 1, YEAR(Today) - 1, YEAR(Today)) VAR PrevMonth = IF(MONTH(Today) = 1, 12, MONTH(Today) - 1) RETURN YEAR([Date]) = PrevMonthYear && MONTH([Date]) = PrevMonth, -- Month Key (YYYYMM format) "Month Key", YEAR([Date]) * 100 + MONTH([Date]), -- Relative Month (0 = current month, negative = past, positive = future) "Relative Month", (YEAR([Date]) - YEAR(Today)) * 12 + (MONTH([Date]) - MONTH(Today)) ) ``` annotation TabularEditor_TableGroup = 03. Dimension TablesTable Groups (Folders for your table)
I was missing 𝗧𝗮𝗯𝗹𝗲 𝗚𝗿𝗼𝘂𝗽𝘀 (Tabular Editor 3 Feature) and structure of tables in Power BI Desktop, so I've decided to finally prioritize the time to figure out how to do it 𝙙𝙮𝙣𝙖𝙢𝙞𝙘𝙖𝙡𝙡𝙮 𝙬𝙞𝙩𝙝 𝙄𝙉𝙁𝙊.𝙑𝙄𝙀𝙒 functions and some basic rule specifications. createOrReplace table 'Table Group DAX' /// COUNTROWS('Table Group DAX') measure 'Number of tables' = COUNTROWS('Table Group DAX') formatString: 0 displayFolder: Measures column Type displayFolder: Attributes summarizeBy: none isNameInferred sourceColumn: [Type] annotation SummarizationSetBy = Automatic column 'Table Name' displayFolder: Attributes summarizeBy: none isNameInferred sourceColumn: [Table Name] annotation SummarizationSetBy = Automatic column Description displayFolder: Attributes summarizeBy: none isNameInferred sourceColumn: [Description] annotation SummarizationSetBy = Automatic column Order formatString: 0 displayFolder: Numeric summarizeBy: none isNameInferred sourceColumn: [Order] annotation SummarizationSetBy = User partition 'Table Group DAX' = calculated mode: import source = ``` -- DAX Script to create table group like information automated using INFO.VIEW DAX functions-- -- Credit to David Kofod Hanna -- -- LinkedIn https://www.linkedin.com/in/davidkofod/ -- -- 11th April 2025-- // Define the name of this table to avoid self-reference VAR _ThisTableName = "Table Group DAX" // // FIELD PARAMETERS TABLES // VAR _FieldParameters = SELECTCOLUMNS( FILTER( INFO.VIEW.TABLES(), CONTAINSSTRING([Expression], "NAMEOF") && [Name] <> _ThisTableName ), "Type", "Field Parameters", "Table Name", [Name], "Description", "Dynamic switch between measure or attributes", "Order", 5 ) // // NUMERIC PARAMETERS // VAR _NumericParameter = SELECTCOLUMNS( FILTER( INFO.VIEW.TABLES(), CONTAINSSTRING([Expression], "GENERATE") && [Name] <> _ThisTableName ), "Type", "Numeric Parameter", "Table Name", [Name], "Description", "Dynamic slider for end users to select", "Order", 6 ) // // CALCULATION GROUPS // VAR _CalculationGroup = SELECTCOLUMNS( FILTER( INFO.VIEW.TABLES(), [CalculationGroupPrecedence] >= 1 && [Name] <> _ThisTableName ), "Type", "Calculation Group", "Table Name", [Name], "Description", "Dynamic calculation items", "Order", 4 ) // // DOCUMENTATION TABLES // VAR _ModelDoc = SELECTCOLUMNS( FILTER( INFO.VIEW.TABLES(), CONTAINSSTRING([Expression], "INFO.VIEW") && [Name] <> _ThisTableName ), "Type", "Model Documentation", "Table Name", [Name], "Description", "Documentation with INFO.VIEW functions", "Order", 7 ) // // FACT TABLES (many side of relationship) // VAR _Fact = DISTINCT( SELECTCOLUMNS( INFO.VIEW.RELATIONSHIPS(), "Type", "Fact", "Table Name", [FromTable], "Description", "Fact with many-side relationship", "Order", 2 ) ) // // DIMENSION TABLES (one side of relationship) // VAR _Dim = DISTINCT( SELECTCOLUMNS( INFO.VIEW.RELATIONSHIPS(), "Type", "Dimension", "Table Name", [ToTable], "Description", "Dimension with one-side relationship", "Order", 3 ) ) // // GET LIST OF ALL TABLES ALREADY CLASSIFIED ABOVE // VAR _AllDefinedTables = UNION( SELECTCOLUMNS(_FieldParameters, "Table Name", [Table Name]), SELECTCOLUMNS(_NumericParameter, "Table Name", [Table Name]), SELECTCOLUMNS(_CalculationGroup, "Table Name", [Table Name]), SELECTCOLUMNS(_ModelDoc, "Table Name", [Table Name]), SELECTCOLUMNS(_Fact, "Table Name", [Table Name]), SELECTCOLUMNS(_Dim, "Table Name", [Table Name]) ) // // GET ALL MODEL TABLES (excluding this one) // VAR _AllTables = SELECTCOLUMNS( FILTER( INFO.VIEW.TABLES(), [Name] <> _ThisTableName ), "Table Name", [Name] ) // // FIND UNCLASSIFIED TABLES (implied as MEASURE GROUPS) // VAR _MeasureGroups = EXCEPT(_AllTables, _AllDefinedTables) VAR _RemainingMeasureGroups = SELECTCOLUMNS( _MeasureGroups, "Type", "Measure Group", "Table Name", [Table Name], "Description", "Classified as Measure Group", "Order", 1 ) // // FINAL UNION OF ALL TABLE GROUP TYPES // VAR _Result = UNION( _FieldParameters, _NumericParameter, _CalculationGroup, _ModelDoc, _Fact, _Dim, _RemainingMeasureGroups ) RETURN _Result ```Calculation group for dynamic date
"𝗚𝗶𝘃𝗲 𝗺𝗲 𝗮𝗻 𝗲𝗮𝘀𝘆 𝗼𝗽𝘁𝗶𝗼𝗻 𝘁𝗼 𝘀𝘄𝗶𝘁𝗰𝗵 𝗱𝗮𝘁𝗲 𝗽𝗲𝗿𝗶𝗼𝗱𝘀 & 𝘾𝙪𝙨𝙩𝙤𝙢 𝙙𝙖𝙩𝙚𝙨" 📅 Let's make it userfriendly and with a click of a button - the end user can set predefined date slicers + 𝘴𝘵𝘪𝘭𝘭 𝘨𝘪𝘷𝘦 𝘵𝘩𝘦𝘮 𝘵𝘩𝘦 𝘰𝘱𝘵𝘪𝘰𝘯 𝘰𝘧 𝘢 𝘤𝘶𝘴𝘵𝘰𝘮 𝘥𝘢𝘵𝘦 𝘳𝘢𝘯𝘨𝘦. All done with 𝗰𝗮𝗹𝗰𝘂𝗹𝗮𝘁𝗶𝗼𝗻 𝗴𝗿𝗼𝘂𝗽𝘀 𝘁𝗼 𝘁𝗵𝗲 𝗿𝗲𝘀𝗰𝘂𝗲 and with some 𝙛𝙞𝙡𝙩𝙚𝙧𝙨 𝘾𝙧𝙚𝙙𝙞𝙩: If I remember correctly, I saw a trick on this custom date range slicer years ago from BI Elite! Let DAX tell the story... createOrReplace table 'Date Slicer' calculationGroup precedence: 1 calculationItem 'Last 30 Days' = VAR __Isdatesfiltered = CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) ) VAR _Day = 30 VAR __Result = IF( __Isdatesfiltered, SELECTEDMEASURE( ), CALCULATE( SELECTEDMEASURE( ), KEEPFILTERS( DATESINPERIOD( 'Date'[Date], TODAY( ), -_Day, DAY ) ) ) ) RETURN __Result calculationItem 'Last 3 Months' = VAR __Isdatesfiltered = CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) ) VAR _Day = 90 VAR __Result = IF( __Isdatesfiltered, SELECTEDMEASURE( ), CALCULATE( SELECTEDMEASURE( ), KEEPFILTERS( DATESINPERIOD( 'Date'[Date], TODAY( ), -_Day, DAY ) ) ) ) RETURN __Result calculationItem 'Last 6 Months' = VAR __Isdatesfiltered = CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) ) VAR __Day = 180 VAR __Result = IF( __Isdatesfiltered, SELECTEDMEASURE( ), CALCULATE( SELECTEDMEASURE( ), KEEPFILTERS( DATESINPERIOD( 'Date'[Date], TODAY( ), -__Day, DAY ) ) ) ) RETURN __Result calculationItem 'Current Year' = VAR __Isdatesfiltered = CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) ) VAR __Result = IF( __Isdatesfiltered, SELECTEDMEASURE( ), CALCULATE( SELECTEDMEASURE( ), KEEPFILTERS( DATESYTD( 'Date'[Date] ) ) ) ) RETURN __Result calculationItem 'Last Year' = VAR __Isdatesfiltered = CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) ) VAR __Result = IF( __Isdatesfiltered, SELECTEDMEASURE( ), CALCULATE( SELECTEDMEASURE( ), KEEPFILTERS( SAMEPERIODLASTYEAR( DATESYTD( 'Date'[Date] ) ) ) ) ) RETURN __Result calculationItem All = VAR __Isdatesfiltered = CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) ) VAR __Result = IF( __Isdatesfiltered, SELECTEDMEASURE( ), CALCULATE( SELECTEDMEASURE( ), REMOVEFILTERS( 'Date'[Date] ) ) ) RETURN __Result calculationItem Custom = VAR __Isdatesfiltered = CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) ) VAR __Result = IF( __Isdatesfiltered, SELECTEDMEASURE( ), CALCULATE( SELECTEDMEASURE( ), REMOVEFILTERS( 'Date'[Date] ) ) ) RETURN __Result measure 'Date period' = MIN('Date'[Date]) & " - " & MAX('Date'[Date]) measure 'Filter Date Slicer Custom' = IF( SELECTEDVALUE ('Date Slicer'[Date slicer column] ) = "Custom", 1, 0 ) formatString: 0 measure 'Title Custom Date Slicer State' = IF( SELECTEDVALUE('Date Slicer'[Date slicer column]) = "Custom", "Choose a custom date range", "Custom selection disabled") column 'Date slicer column' dataType: string sourceColumn: Name sortByColumn: Ordinal column Ordinal dataType: int64 isHidden sourceColumn: OrdinalCalculation Group for dynamic weeks
"𝗚𝗶𝘃𝗲 𝗺𝗲 𝗮𝗻 𝗲𝗮𝘀𝘆 𝗼𝗽𝘁𝗶𝗼𝗻 𝘁𝗼 𝘀𝘄𝗶𝘁𝗰𝗵 🅦🅔🅔🅚🅢" 📅 Let's make it userfriendly and with a click of a button - the end user can set predefined date slicers. All done with 𝗰𝗮𝗹𝗰𝘂𝗹𝗮𝘁𝗶𝗼𝗻 𝗴𝗿𝗼𝘂𝗽𝘀 𝘁𝗼 𝘁𝗵𝗲 𝗿𝗲𝘀𝗰𝘂𝗲 and with help from the extended date table by Melissa de Korte for some OFFSET columns to assist us with weeks and easier DAX. Let DAX tell the story... createOrReplace table 'Week Date Slicer' calculationGroup precedence: 2 calculationItem 'Current Week' = VAR _Isdatesfiltered = CALCULATE ( ISFILTERED ( 'Date'[Date] ), ALLSELECTED () ) VAR _Week = MAX('Date'[CurrWeekOffset]) VAR _Result = IF ( _Isdatesfiltered, SELECTEDMEASURE (), CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS( 'Date'[CurrWeekOffset] = _Week ) ) ) RETURN _Result calculationItem LW = VAR _Isdatesfiltered = CALCULATE ( ISFILTERED ( 'Date'[Date] ), ALLSELECTED () ) VAR _MaxWeek = CALCULATE(MAX('Date'[CurrWeekOffset]), 'Date'[WeekCompleted] = TRUE) VAR _Week = MAX('Date'[CurrWeekOffset]) - 1 VAR _Result = IF ( _Isdatesfiltered, SELECTEDMEASURE (), CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS( 'Date'[CurrWeekOffset] >= _Week && 'Date'[CurrWeekOffset] <= _MaxWeek ) ) ) RETURN _Result calculationItem L2W = VAR _Isdatesfiltered = CALCULATE ( ISFILTERED ( 'Date'[Date] ), ALLSELECTED () ) VAR _MaxWeek = CALCULATE(MAX('Date'[CurrWeekOffset]), 'Date'[WeekCompleted] = TRUE) VAR _Week = MAX('Date'[CurrWeekOffset]) - 2 VAR _Result = IF ( _Isdatesfiltered, SELECTEDMEASURE (), CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS( 'Date'[CurrWeekOffset] >= _Week && 'Date'[CurrWeekOffset] <= _MaxWeek ) ) ) RETURN _Result calculationItem L4W = VAR _Isdatesfiltered = CALCULATE ( ISFILTERED ( 'Date'[Date] ), ALLSELECTED () ) VAR _MaxWeek = CALCULATE(MAX('Date'[CurrWeekOffset]), 'Date'[WeekCompleted] = TRUE) VAR _Week = MAX('Date'[CurrWeekOffset]) - 4 VAR _Result = IF ( _Isdatesfiltered, SELECTEDMEASURE (), CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS( 'Date'[CurrWeekOffset] >= _Week && 'Date'[CurrWeekOffset] <= _MaxWeek ) ) ) RETURN _Result calculationItem L12W = VAR _Isdatesfiltered = CALCULATE ( ISFILTERED ( 'Date'[Date] ), ALLSELECTED () ) VAR _MaxWeek = CALCULATE(MAX('Date'[CurrWeekOffset]), 'Date'[WeekCompleted] = TRUE) VAR _Week = MAX('Date'[CurrWeekOffset]) - 12 VAR _Result = IF ( _Isdatesfiltered, SELECTEDMEASURE (), CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS( 'Date'[CurrWeekOffset] >= _Week && 'Date'[CurrWeekOffset] <= _MaxWeek ) ) ) RETURN _Result calculationItem All = VAR _Isdatesfiltered = CALCULATE ( ISFILTERED ( 'Date'[Date] ), ALLSELECTED () ) VAR _Result = IF ( _Isdatesfiltered, SELECTEDMEASURE (), CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Date'[Date] ) ) ) RETURN _Result calculationItem Custom = VAR __Isdatesfiltered = CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) ) VAR __Result = IF( __Isdatesfiltered, SELECTEDMEASURE( ), CALCULATE( SELECTEDMEASURE( ), REMOVEFILTERS( 'Date'[Date] ) ) ) RETURN __Result measure 'Date period' = MIN('Date'[Date]) & " - " & MAX('Date'[Date]) measure 'Filter Date Slicer Custom' = IF( SELECTEDVALUE ('Week Date Slicer'[Week Date] ) = "Custom", 1, 0 ) formatString: 0 measure 'Title Custom Date Slicer State' = IF( SELECTEDVALUE('Week Date Slicer'[Week Date]) = "Custom", "Choose a custom date range", "Custom selection disabled") measure 'Week period' = MIN('Date'[Week & Year]) & " - " & MAX('Date'[Week & Year]) column 'Week Date' dataType: string sourceColumn: Namea sortByColumn: Ordinal column Ordinal dataType: int64 isHidden sourceColumn: OrdinalLast Refresh Semantic Model (Power Query)
createOrReplace /// Last refresh of semantic model - not the underlying job and pipelines table 'Last Refresh' column TimeStamp dataType: dateTime formatString: d mmm yyyy hh.nn displayFolder: Dates summarizeBy: none sourceColumn: TimeStamp annotation SummarizationSetBy = Automatic annotation PBI_FormatHint = {"isDateTimeCustom":true} column 'Date Last Refresh' dataType: dateTime formatString: Long Date displayFolder: Dates summarizeBy: none sourceColumn: Date Last Refresh annotation SummarizationSetBy = Automatic annotation UnderlyingDateTimeDataType = Date column 'Time Last Refresh' dataType: dateTime formatString: Long Time displayFolder: Dates summarizeBy: none sourceColumn: Time Last Refresh annotation SummarizationSetBy = Automatic annotation UnderlyingDateTimeDataType = Time partition 'Last Refresh' = m mode: import queryGroup: 'Meta Data & Organize' source = /* M-Query - Data Last Refreshed */ let SummerTime = Date.StartOfWeek( #date(Date.Year( DateTime.LocalNow() ), 3, 31) , Day.Sunday ), WinterTime = Date.StartOfWeek( #date(Date.Year( DateTime.LocalNow() ), 10, 31) , Day.Sunday ), CurrentDateTime = DateTimeZone.RemoveZone( DateTimeZone.UtcNow() ), TimeShiftFromUTC = if CurrentDateTime < SummerTime & #time( 2, 0, 0) or CurrentDateTime > WinterTime & #time( 2, 0, 0) then 1 else 2, TimeStamp = DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), TimeShiftFromUTC, 0), #"Converted to Table" = #table(1, {{TimeStamp}}), #"Renamed Column1 to TimeStamp" = Table.RenameColumns(#"Converted to Table",{{"Column1", "TimeStamp"}}), #"Changed Type TimeStamp" = Table.TransformColumnTypes(#"Renamed Column1 to TimeStamp",{{"TimeStamp", type datetimezone}}), #"Inserted Col Date" = Table.AddColumn(#"Changed Type TimeStamp", "Date", each DateTime.Date([TimeStamp]), type date), #"Inserted Col Time" = Table.AddColumn(#"Inserted Col Date", "Time", each DateTime.Time([TimeStamp]), type time), #"Renamed Col Date and Time" = Table.RenameColumns(#"Inserted Col Time",{{"Date", "Date Last Refresh"}, {"Time", "Time Last Refresh"}}) in #"Renamed Col Date and Time"
