Data Analysis Expressions - Customized Martix Table
This Power BI report display historical trends from 2018–2023 of the Ozone Season Heat Input (MMBtu) across U.S. states and fuel types. It highlight the developement of a customized matrix visual for displaying the Yearly record along with the CAGR %, shared %, and YOY % on a Martix table with a dynamic state slicer for environmental compliance and emissions analysis across states. This is a continuous project : 1st : Adjusting-Unadjusted-Emissions-with-AI-Solution 2nd : Updated view - 2022 Unadjusted NOx Emissions _M KPI_Selector_Value = VAR _id = SELECTEDVALUE ( KPI_MatrixAxis[ID] ) VAR _display = SELECTEDVALUE ( KPI_MatrixAxis[Display] ) VAR _year = IF ( _id = 1, VALUE ( _display ) ) VAR YearlyTotal = CALCULATE ( [Total HTIOZ], REMOVEFILTERS ( 'Unadjusted Annual Unit'[Data Year] ), KEEPFILTERS ( 'Unadjusted Annual Unit'[Data Year] = _year ) ) VAR YoY = [_YOY % HTIOZD Data] VAR CAGR = [3Y CAGR HTIOZ] VAR Share_of_Total = DIVIDE ( [Total HTIOZ], CALCULATE ( [Total HTIOZ], ALLSELECTED ( 'Unadjusted Annual Unit' ) ) ) RETURN SWITCH ( TRUE(), _id = 1, FORMAT(YearlyTotal,"#,##"), _id = 2, YoY , --FORMAT(YoY,"0.0%"), _id = 3, CAGR, -- FORMAT(CAGR,"0.0%"), _id = 4, FORMAT(Share_of_Total,"0.0%"), BLANK() ) eyJrIjoiYTMzYWI3NWYtM2RjZC00MzRhLWIzNmUtYWQ0MWY2Mjc4MWEzIiwidCI6IjNlMjFhMTFlLTc3MDctNDdmOC1iMzRhLTc5YTQ2YTQ0ZTk5MyIsImMiOjF9Date table with Calendar and Fiscal year
DateFiscal = ADDCOLUMNS ( CALENDAR ( MIN ( 'Table'[Date column] ), MAX ( 'Table'[Date column] ) ), "MonthNo", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ), "MonthYear", FORMAT ( [Date], "MMMM YYYY" ), "MonthYearShort", FORMAT ( [Date], "MMM YY" ), "MonthYearNo", FORMAT ( [Date], "YYYYMM" ), "Quarter", QUARTER ( [Date] ), "Year", YEAR ( [Date] ), "QuarterYear", "Q" & FORMAT ( QUARTER ( [Date] ), "0" ) & " " & FORMAT ( YEAR ( [Date] ), "0000" ), "YearQuarterNo", FORMAT ( YEAR ( [Date] ), "0000" ) & FORMAT ( QUARTER ( [Date] ), "00" ), "Day", DAY ( [Date] ), "WeekNumber", WEEKNUM ( [Date] ), "WeekdayNum", WEEKDAY ( [Date] ), "WeekdayName", FORMAT ( [Date], "DDDD" ), "PreviousWeek", WEEKNUM ( [Date] ) -1 , "WeekStartDate", ([Date] - WEEKDAY ( [Date] , 1 ) +1), "WeekEndDate", ([Date] - WEEKDAY ( [Date] , 1 ) +7), "YearMonth", FORMAT ( [Date], "YYYY-M" ), "FiscalQuarter", SWITCH ( TRUE(), MONTH ( [Date] ) IN {8, 9, 10}, "Q1", MONTH ( [Date] ) IN {11, 12, 1}, "Q2", MONTH ( [Date] ) IN {2, 3, 4}, "Q3", MONTH ( [Date] ) IN {5, 6, 7}, "Q4" ), "FiscalYear", YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 8, 1, 0 ), 8, 1 ) ), "FiscalQuarterNo", FORMAT ( YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 8, 1, 0 ), 8, 1 ) ), "0000" ) & SWITCH ( TRUE(), MONTH ( [Date] ) IN {8, 9, 10}, "01", MONTH ( [Date] ) IN {11, 12, 1}, "02", MONTH ( [Date] ) IN {2, 3, 4}, "03", MONTH ( [Date] ) IN {5, 6, 7}, "04" ), "FiscalQuarterYear", SWITCH ( TRUE(), MONTH ( [Date] ) IN {8, 9, 10}, "Q1", MONTH ( [Date] ) IN {11, 12, 1}, "Q2", MONTH ( [Date] ) IN {2, 3, 4}, "Q3", MONTH ( [Date] ) IN {5, 6, 7}, "Q4" ) & " " & FORMAT ( YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 8, 1, 0 ), 8, 1 ) ), "0000" ) )Days of Supply
Suppose you have a weekly forecast of inventory and demand and you wish to know for each week the number of days of supply that you have on hand. That is the purpose of this Quick Measure. Inputs are the current week and inventory as well as the demand column. Days of Supply = // Get the current week and inventory for the current row VAR __week = MAX([Week]) VAR __inventory = MAX([Ending on hand Inventory]) // Create a table of all weeks greater than the current week VAR __table = FILTER(ALL(Inventory),[Week]>__week) // Add our current inventory from above to each row VAR __table1 = ADDCOLUMNS(__table,"__start",__inventory) // Add a running total of demand to each row VAR __table2 = ADDCOLUMNS(__table1,"__demand",SUMX(FILTER(__table1,[Week]<=EARLIER([Week])),[Demand])) // Add the difference in start versus the running total of demand to each row VAR __table3 = ADDCOLUMNS(__table2,"__left",[__start] - [__demand]) // Create a table that only has the positive rows VAR __table4 = FILTER(__table3,[__left]>=0) // With only the positive rows, the MIN is the last row before demand runs out VAR __min = MINX(__table4,[__left]) // Therefore, our base days is the number of rows in this table * 7 VAR __baseDays = COUNTROWS(__table4)*7 // Grab the MAX value of the negative rows, this is the row right after our inventory runs out VAR __max = MAXX(FILTER(__table3,[__left]<0),[__left]) // Divide the row right before the invetory ran out by the sum of the absolute values of right before and after // the inventory ran out. This is the percentage of days in that week before inventory ran out. multiply this by 7 // and this is the number of days in that week before inventory ran out VAR __extraDays = __min / (__min + ABS(__max)) * 7 RETURN __baseDays + __extraDays Interestingly, this Quick Measure exhibits a form of "looping" in DAX, or at least a work-a-round. Consider that a primary task of this measure is to determin the week in which inventory "runs out". In traditional programming, one would determine this with something like a for or while loop, checking for a boundary condition of the inventory on hand becoming negative with respect to demand. However, in DAX, there are no for or while looping constructs. Thus, instead we create a temporary table where each row in the table represents one pass or iteration through a traditional programming "loop". We can then use our boundary condition to filter down to the specific rows where that boundary condition occurs in order to perform our calculation. As demonstrated in the DAX code above, we can determine the values on either side of our boundary condition as well as how many "interations" were required in order to hit that boundary condition. eyJrIjoiZDcxY2U3ZjAtM2ZiMy00ZjJhLWE0N2YtZTM5YjFiNDJlMTJlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Periodic Billing
Again, thanks to @Phil_Seamark's insightful guidance and examples in his fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I finally "get" the GENERATE function and how it can be used to elegantly solve problems that have vexed me since almost the very first Power BI model that I ever built, dealing with data that contains date ranges. This one uses the same technique as Open Tickets but puts a different spin on it by also requiring that there be a periodic element to the totals calculation. The following measure assumes a disconnected date table and data that involves billing starting and ending dates with a monthly fee. The measure computes the total revenue within any particular month, which can then be plotted. Also nifty. Total Amount = VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#"))) VAR tmpBilling = ADDCOLUMNS('Billing',"MonthYearBegin",VALUE(YEAR([BeginDate]) & FORMAT(MONTH([BeginDate]),"0#")), "MonthYearEnd",VALUE(YEAR([UntilDate]) & FORMAT(MONTH([UntilDate]),"0#"))) VAR tmpTable = SELECTCOLUMNS( FILTER( GENERATE( tmpBilling, SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear]) ), [MonthYear] >= [MonthYearBegin] && [MonthYear] <= [MonthYearEnd] ), "Customer",[Customer], "Year",[Year], "Month",[Month], "Amount",[Amount] ) RETURN SUMX(tmpTable,[Amount]) Again, if you are only going to own one DAX book, IMHO, Phil's is the book you want! eyJrIjoiN2IyMGNlYmItZjhjNi00M2IxLWI1MDAtZmVkMzIxMjkzNmFhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Chelsie Eiden's Duration
Chelsie Eiden is my new favorite human being on the face of the planet. I don't know her major but, even if she is majoring in math, it still wouldn't change my mind on this one. That's how much I like this individual. The reason she is my favorite human being on the face of the planet is because she has finally...FINALLY, solved a "problem" with Power BI that is, ohhhh, say at least 4 or 5 years old. Since the dawn of Power BI there has been this problem with aggregating duration in HH : MM : SS format. You could convert it to seconds to aggregate it but you couldn't display it in the hours, minutes, seconds format in a visual that properly aggregated it in column charts because the minute you did a concatenation or a format on it, "POOF" it became text. Maddening!! I have been harping on this issue for, well, forever, such as in this post I did with konstantinos ages ago. So, Chelsie, thank-you, thank-you, thank-you from the bottom of my heart! I have named this new Quick Measure just for you. Chelsie Eiden's Duration = // Duration formatting // * @konstatinos 1/25/2016 // * Given a number of seconds, returns a format of "hh:mm:ss" // // We start with a duration in number of seconds VAR Duration = SUM([Duration]) // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number RETURN // We put the hours, minutes and seconds into the proper "place" Hours * 10000 + Minutes * 100 + Seconds All but the last line is the code from that article that konstantinos and I wrote years and years ago. The only difference is the last line. Once you have this measure, then all you have to do is implement Chelsie Eiden's Custom Format String with a value of "00:00:00" (no double quotes). Boom!! https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-2019-feature-summary/#customFormatStrings eyJrIjoiYjE5ZDZkN2EtODdlNy00ZmUxLWIyOGItOWRhYjU0NDY2Y2VhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Ageing Table
Ageing table in DAX Calendar Ageing = VAR _today_date = TODAY() //'Properties'[Today Date] VAR _min_date1 = CALCULATE(MIN(Table1[Date1])) VAR _min_date2 = CALCULATE(MIN(Table2[Date2])) VAR _future_date = IF(_min_date1 < _min_date2, _min_date1, _min_date2) //VAR _future_date = DATE( YEAR( _today_date ) - 6, 01, 01 ) VAR _result = UNION ( ADDCOLUMNS (CALENDAR ( _today_date - 30, _today_date), "Ageing Days", "0 to 30", "Ageing Days Order", 1) , ADDCOLUMNS (CALENDAR ( _today_date - 60, _today_date - 31 ), "Ageing Days", "31 to 60", "Ageing Days Order", 2) , ADDCOLUMNS (CALENDAR ( _today_date - 90, _today_date - 61 ), "Ageing Days", "61 to 90", "Ageing Days Order", 3) , ADDCOLUMNS (CALENDAR ( _today_date - 120, _today_date - 91 ), "Ageing Days", "91 to 120", "Ageing Days Order", 4) , ADDCOLUMNS (CALENDAR ( _future_date, _today_date - 121), "Ageing Days", "120+", "Ageing Days Order", 5) ) RETURN _resultTimeframe Table
Timeframe table in DAX Calendar Timeframe = VAR _today_date = TODAY() //'Properties'[Today Date] VAR _yesterday_date = _today_date - 1 VAR _week_start = _today_date - WEEKDAY ( _today_date, 2 ) VAR _week_end = _today_date - WEEKDAY ( _today_date, 2 ) + 6 VAR _month_start = DATE( YEAR(_today_date), MONTH(_today_date), 01 ) VAR _month_end = EOMONTH( _today_date, 0) VAR _quarter_start = DATE ( YEAR (_today_date), ROUNDUP ( DIVIDE ( MONTH (_today_date), 3 ), 0 ) * 3 - 2, 1 ) VAR _quarter_end = EOMONTH(EDATE(_quarter_start, 2), 0) VAR _fiscal_year = YEAR(EDATE( _today_date, 6)) VAR _fiscal_year_start = DATE( _fiscal_year - 1, 07, 01) VAR _fiscal_year_end = DATE( _fiscal_year, 06, 30) VAR _tomorrow_date = IF(_today_date + 1 > _fiscal_year_end, _fiscal_year_end, _today_date + 1) VAR _calendar_year = YEAR(_today_date) VAR _calendar_year_start = DATE( _calendar_year , 01, 01) VAR _calendar_year_end = DATE( _calendar_year, 12, 31) VAR _previous_month_start = IF(MONTH(_today_date) = 1, DATE(YEAR(_today_date)-1, 12, 1), DATE(YEAR(_today_date), MONTH(_today_date)-1, 1)) VAR _previous_month_end = DATE(YEAR(_previous_month_start), MONTH(_previous_month_start), DAY(EOMONTH(_previous_month_start, 0))) VAR _previous_quarter_start = EDATE(_quarter_start, -3) VAR _previous_quarter_end = EOMONTH(EDATE(_quarter_start, -1), 0) VAR _previous_fiscal_year_start = DATE( _fiscal_year - 2, 07, 01) VAR _previous_fiscal_year_end = DATE( _fiscal_year - 1, 06, 30) VAR _previous_calendar_year_start = DATE( _calendar_year - 1, 01, 01) VAR _previous_calendar_year_end = DATE( _calendar_year - 1, 12, 31) VAR _today_date_py = DATE( YEAR(_today_date) - 1, MONTH(_today_date), DAY(_today_date) ) VAR _week_start_py = DATE( YEAR(_today_date_py), 1 , 1) + (WEEKNUM(_today_date_py) - 1 ) * 7 VAR _month_start_py = DATE( YEAR(_today_date_py), MONTH(_today_date), 01 ) VAR _quarter_start_py = DATE( YEAR(_quarter_start) - 1, MONTH(_quarter_start), 01 ) VAR _fiscal_year_start_py = DATE( YEAR(_fiscal_year_start) - 1, MONTH(_fiscal_year_start), 01 ) VAR _result = UNION ( ADDCOLUMNS (CALENDAR ( _today_date, _today_date), "Timeframe", "Today", "Timeframe Order", 1 ) , ADDCOLUMNS (CALENDAR ( _yesterday_date, _yesterday_date), "Timeframe", "Yesterday", "Timeframe Order", 2 ) // Week , ADDCOLUMNS (CALENDAR ( _week_start - 7, _week_end - 7 ), "Timeframe", "Previous Week", "Timeframe Order", 3 ) , ADDCOLUMNS (CALENDAR ( _week_start, _week_end ), "Timeframe", "Current Week", "Timeframe Order", 4 ) , ADDCOLUMNS (CALENDAR ( _week_start, _today_date ), "Timeframe", "WTD", "Timeframe Order", 5 ) , ADDCOLUMNS (CALENDAR ( _week_start_py, _today_date_py ), "Timeframe", "WTD Previous Year", "Timeframe Order", 6 ) // Month , ADDCOLUMNS (CALENDAR ( _previous_month_start, _previous_month_end ), "Timeframe", "Previous Month", "Timeframe Order", 7 ) , ADDCOLUMNS (CALENDAR ( _month_start, _month_end ), "Timeframe", "Current Month", "Timeframe Order", 8 ) , ADDCOLUMNS (CALENDAR ( _month_start, _today_date ), "Timeframe", "MTD", "Timeframe Order", 9 ) , ADDCOLUMNS (CALENDAR ( _month_start_py, _today_date_py ), "Timeframe", "MTD Previous Year", "Timeframe Order", 10 ) // Quarter , ADDCOLUMNS (CALENDAR ( _previous_quarter_start, _previous_quarter_end ), "Timeframe", "Previous Qtr", "Timeframe Order", 11 ) , ADDCOLUMNS (CALENDAR ( _quarter_start, _quarter_end ), "Timeframe", "Current Qtr", "Timeframe Order", 12 ) , ADDCOLUMNS (CALENDAR ( _quarter_start, _today_date ), "Timeframe", "QTD", "Timeframe Order", 13 ) , ADDCOLUMNS (CALENDAR ( _quarter_start_py, _today_date_py ), "Timeframe", "QTD Previous Year", "Timeframe Order", 14 ) // Financial Year , ADDCOLUMNS (CALENDAR ( _previous_fiscal_year_start, _previous_fiscal_year_end ), "Timeframe", "Previous Fiscal Year", "Timeframe Order", 15 ) , ADDCOLUMNS (CALENDAR ( _fiscal_year_start_py, _today_date_py ), "Timeframe", "YTD Previous Fiscal Year", "Timeframe Order", 16 ) , ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _fiscal_year_end ), "Timeframe", "Current Fiscal Year", "Timeframe Order", 17 ) , ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _today_date ), "Timeframe", "YTD Fiscal", "Timeframe Order", 18 ) , ADDCOLUMNS (CALENDAR ( _tomorrow_date, _fiscal_year_end ), "Timeframe", "Rest of Fiscal Year", "Timeframe Order", 19 ) // Calendar Year , ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _previous_calendar_year_end ), "Timeframe", "Previous Calendar Year", "Timeframe Order", 20 ) , ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _today_date_py ), "Timeframe", "YTD Previous Calendar Year", "Timeframe Order", 21 ) , ADDCOLUMNS (CALENDAR ( _calendar_year_start, _calendar_year_end ), "Timeframe", "Current Calendar Year", "Timeframe Order", 22 ) , ADDCOLUMNS (CALENDAR ( _calendar_year_start, _today_date ), "Timeframe", "YTD Calendar", "Timeframe Order", 23 ) , ADDCOLUMNS (CALENDAR ( _tomorrow_date, _calendar_year_end ), "Timeframe", "Rest of Calendar Year", "Timeframe Order", 24 ) ) RETURN _resultCalendar Table
Calendar table in DAX Calendar = VAR _today_date = TODAY() //'Properties'[Today Date] VAR _fiscal_year = YEAR(EDATE( _today_date, 6)) VAR _fiscal_year_start = DATE ( _fiscal_year - 1, 07, 01) VAR _fiscal_year_end = DATE ( _fiscal_year, 06, 30) VAR _result = ADDCOLUMNS ( //CALENDARAUTO() //range of dates is calculated automatically based on data in the model CALENDAR(_fiscal_year_start, _fiscal_year_end) , "Calendar Year Period End", FORMAT([Date], "yyyy12") , "Calendar Year Period Start", FORMAT([Date], "yyyy01") , "Calendar Year Period", FORMAT([Date], "yyyyMM") , "Calendar Year Quarter Nbr", QUARTER([Date]) , "Calendar Year Quarter", FORMAT([Date], "\C\Yyyyy \Qq") , "Calendar Year Half", FORMAT([Date], "\F\Yyyyy \H") & ROUNDUP(MONTH([Date]) / 6, 0) , "Calendar Year", YEAR([Date]) , "Day Name Short", FORMAT([Date], "DDD") , "Day Name", FORMAT([Date], "DDDD") , "Day Of Week", WEEKDAY([Date]) , "Day", DAY([Date]) , "Fiscal Year Period End", FORMAT(EDATE([Date], 6), "yyyy12") , "Fiscal Year Period Start", FORMAT(EDATE([Date], 6), "yyyy01") , "Fiscal Year Period", FORMAT(EDATE([Date], 6), "yyyyMM") , "Fiscal Year Quarter Nbr", FORMAT(EDATE([Date], 6), "q") , "Fiscal Year Quarter", FORMAT(EDATE([Date], 6), "\F\Yyyyy \Qq") , "Fiscal Year Half", FORMAT(EDATE([Date], 6), "\F\Yyyyy \H") & ROUNDUP(MONTH(EDATE([Date], 6)) / 6, 0) , "Fiscal Year", YEAR(EDATE([Date], 6)) , "Is Current FY", IF(YEAR(EDATE([Date], 6)) = _fiscal_year, 1, 0) , "Is Future", IF([Date] > _today_date, 1, 0) , "Is Past", IF([Date] < _today_date, 1, 0) , "Month End", EOMONTH([Date], 0) , "Month Name Short", FORMAT([Date], "MMM") , "Month Name", FORMAT([Date], "MMMM") , "Month Start", DATE(YEAR([Date]), MONTH([Date]), 1) , "Month", MONTH([Date]) , "Week Ending", [Date] + 7 - WEEKDAY([Date], 1) // Saturday , "Week Starting", [Date] - WEEKDAY([Date], 1) + 1 // Sunday , "Week Day", WEEKDAY([Date], 2) , "Week of Month", 1 + WEEKNUM([Date]) - WEEKNUM( EOMONTH([Date], -1 ) + 1 ) , "Week of Year", WEEKNUM([Date]) , "Week of Fiscal Year", IF(MONTH([Date]) < 7 , WEEKNUM([Date], 1) + (WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) - 1) , WEEKNUM([Date], 1) - WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) + 1) ) RETURN _result Calendar table in M let //Set the following variables Culture = "English (United States)", //Select a culture. UseYesterdayAsCurrentDate = true, //true = yesterday, false = today YearsBack = 6, //How many years to include prior to the current year. YearsAhead = 6, //How many years to include after the current year. FyYearsBack = 2, //How many fiscal years to include prior to the current year. FyYearsAhead = 2, //How many fiscal years to include after the current year. GoToBeginning = "Year", //Options: Year, Month, None GoToEnd = "Year", //Options: Year, Month, None //Figure the Start and End Dates, based on above variables DateToday = DateTime.Date(DateTime.LocalNow()), CurrentDate = if UseYesterdayAsCurrentDate = true then Date.AddDays(DateToday, -1) else DateToday, YearBegin = Date.Year(CurrentDate) - YearsBack, MonthBegin = if GoToBeginning = "Year" then 1 else Date.Month(CurrentDate), DayBegin = if GoToBeginning = "None" then Date.Day(CurrentDate) else 1, StartDate = #date(YearBegin, MonthBegin, DayBegin), YearEnd = Date.Year(CurrentDate) + YearsAhead, MonthEnd = if GoToEnd = "Year" then 12 else Date.Month(CurrentDate), DayEndTemp = if GoToEnd = "Year" then 31 else Date.Day(CurrentDate), EndDateTemp = #date(YearEnd, MonthEnd, DayEndTemp), EndDate = if GoToEnd = "Month" then DateTime.Date(Date.EndOfMonth(EndDateTemp)) else EndDateTemp, DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1, //Get complete list of dates, Convert to a table, update name and data type AllDates = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), TableFromList = Table.FromList(AllDates, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), //Add other attributes of the date, as desired InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])), InsertQuarterOfYear = Table.AddColumn(InsertYear, "Quarter Of Year", each Date.QuarterOfYear([Date])), InsertMonthOfYear = Table.AddColumn(InsertQuarterOfYear, "Month Of Year", each Date.Month([Date])), InsertDayOfMonth = Table.AddColumn(InsertMonthOfYear, "Day Of Month", each Date.Day([Date])), InsertDayOfWeek = Table.AddColumn(InsertDayOfMonth, "Day Of Week", each Date.DayOfWeek([Date])), InsertDateAlternateKey = Table.AddColumn(InsertDayOfWeek, "Date Alternate Key", each [Year] * 10000 + [Month Of Year] * 100 + [Day Of Month]), InsertMonthName = Table.AddColumn(InsertDateAlternateKey, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text), InsertMonthKey = Table.AddColumn(InsertMonthName, "Month Key", each [Year] * 100 + [Month Of Year]), InsertMonthYear = Table.AddColumn(InsertMonthKey, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year])), InsertMonthStart = Table.AddColumn(InsertMonthYear, "Month Start", each Date.StartOfMonth([Date]), type date), InsertMonthEnd = Table.AddColumn(InsertMonthStart, "Month Ending", each Date.EndOfMonth([Date]), type date), InsertQuarterName = Table.AddColumn(InsertMonthEnd, "Quarter Name", each "Q" & Number.ToText([Quarter Of Year])), InsertQuarterKey = Table.AddColumn(InsertQuarterName, "Quarter Key", each [Year] * 100 + [Quarter Of Year]), InsertQuarterYear = Table.AddColumn(InsertQuarterKey, "Quarter Year", each "Q" & Number.ToText([Quarter Of Year]) & " " & Number.ToText([Year])), InsertDayName = Table.AddColumn(InsertQuarterYear, "Day Of Week Name", each Date.ToText([Date], "dddd", Culture), type text), InsertWeekEnding = Table.AddColumn(InsertDayName, "Week Ending", each Date.EndOfWeek([Date]), type date), //Add Relative Date Positions. InsertRelativeYear = Table.AddColumn(InsertWeekEnding, "Relative Year", each [Year]-Date.Year(CurrentDate)), InsertRelativeYearDescription = Table.AddColumn(InsertRelativeYear, "Relative Year Description", each if [Relative Year] = 0 then "Current Year" else if [Relative Year] = -1 then "Last Year" else if [Relative Year] = 1 then "Next Year" else if [Relative Year] < -1 then Number.ToText(Number.Abs([Relative Year])) & " Years Back" else Number.ToText([Relative Year]) & " Years Ahead"), InsertRelativeQuarter = Table.AddColumn(InsertRelativeYearDescription, "Relative Quarter", each 4*([Year]-Date.Year(CurrentDate)) + ([Quarter Of Year]-Date.QuarterOfYear(CurrentDate))), InsertRelativeQuarterDescription = Table.AddColumn(InsertRelativeQuarter, "Relative Quarter Description", each if [Relative Quarter] = 0 then "Current Quarter" else if [Relative Quarter] = -1 then "Last Quarter" else if [Relative Quarter] = 1 then "Next Quarter" else if [Relative Quarter] < -1 then Number.ToText(Number.Abs([Relative Quarter])) & " Quarters Back" else Number.ToText([Relative Quarter]) & " Quarters Ahead"), InsertRelativeMonth = Table.AddColumn(InsertRelativeQuarterDescription, "Relative Month", each 12*([Year]-Date.Year(CurrentDate)) + ([Month Of Year]-Date.Month(CurrentDate))), InsertRelativeMonthDescription = Table.AddColumn(InsertRelativeMonth, "Relative Month Description", each if [Relative Month] = 0 then "Current Month" else if [Relative Month] = -1 then "Last Month" else if [Relative Month] = 1 then "Next Month" else if [Relative Month] < -1 then Number.ToText(Number.Abs([Relative Month])) & " Months Back" else Number.ToText([Relative Month]) & " Months Ahead"), InsertRelativeWeek = Table.AddColumn(InsertRelativeMonthDescription, "Relative Week", each Duration.Days(Duration.From([Week Ending]-Date.EndOfWeek(CurrentDate)))/7), InsertRelativeWeekDescription = Table.AddColumn(InsertRelativeWeek, "Relative Week Description", each if [Relative Week] = 0 then "Current Week" else if [Relative Week] = -1 then "Last Week" else if [Relative Week] = 1 then "Next Week" else if [Relative Week] < -1 then Number.ToText(Number.Abs([Relative Week])) & " Weeks Back" else Number.ToText([Relative Week]) & " Weeks Ahead"), InsertRelativeDay = Table.AddColumn(InsertRelativeWeekDescription, "Relative Day", each Duration.Days(Duration.From([Date]-CurrentDate))), InsertRelativeDayDescription = Table.AddColumn(InsertRelativeDay, "Relative Day Description", each if [Relative Day] = 0 then "Current Day" else if [Relative Day] = -1 then "Last Day" else if [Relative Day] = 1 then "Next Day" else if [Relative Day] < -1 then Number.ToText(Number.Abs([Relative Day])) & " Days Back" else Number.ToText([Relative Day]) & " Days Ahead"), //Add Date Category Positions. InsertYearGroup = Table.AddColumn(InsertRelativeDayDescription, "Year Group", each if [Relative Year] = 0 then "Current Year" else if [Relative Year] < 0 then "Past Years" else "Future Years"), InsertQuarterGroup = Table.AddColumn(InsertYearGroup, "Quarter Group", each if [Relative Quarter] = 0 then "Current Quarter" else if [Relative Quarter] < 0 then "Past Quarters" else "Future Quarters"), InsertMonthGroup = Table.AddColumn(InsertQuarterGroup, "Month Group", each if [Relative Month] = 0 then "Current Month" else if [Relative Month] < 0 then "Past Months" else "Future Months"), InsertWeekGroup = Table.AddColumn(InsertMonthGroup, "Week Group", each if [Relative Week] = 0 then "Current Week" else if [Relative Week] < 0 then "Past Weeks" else "Future Weeks"), InsertDayGroup = Table.AddColumn(InsertWeekGroup, "Day Group", each if [Relative Day] = 0 then "Current Day" else if [Relative Day] < 0 then "Past Days" else "Future Days"), ChangedType2 = Table.TransformColumnTypes(InsertDayGroup,{{"Year", Int64.Type}, {"Quarter Of Year", Int64.Type}, {"Month Of Year", Int64.Type}, {"Day Of Month", Int64.Type}, {"Day Of Week", Int64.Type}, {"Date Alternate Key", Int64.Type}, {"Quarter Name", type text}, {"Month Year", type text}, {"Quarter Year", type text}, {"Relative Year Description", type text}, {"Relative Quarter Description", type text}, {"Relative Month Description", type text}, {"Relative Week Description", type text}, {"Relative Day Description", type text}, {"Year Group", type text}, {"Quarter Group", type text}, {"Month Group", type text}, {"Week Group", type text}, {"Day Group", type text}, {"Relative Day", Int64.Type}, {"Relative Week", Int64.Type}, {"Relative Month", Int64.Type}, {"Relative Quarter", Int64.Type}, {"Relative Year", Int64.Type}, {"Quarter Key", Int64.Type}, {"Month Key", Int64.Type}}), //Add Fiscal Periods InsertFiscalYear = Table.AddColumn(ChangedType2, "Fiscal Year", each Date.ToText(Date.AddMonths([Date], 6), "yyyy")), InsertPeriod = Table.AddColumn(InsertFiscalYear, "Period", each Date.ToText(Date.AddMonths([Date], 6), "yyyyMM")), InsertPeriodFyStart = Table.AddColumn(InsertPeriod, "Fiscal Year Period Start", each Date.ToText(Date.AddMonths([Date], 6), "yyyy01")), InsertPeriodFyEnd = Table.AddColumn(InsertPeriodFyStart, "Fiscal Year Period End", each Date.ToText(Date.AddMonths([Date], 6), "yyyy12")), InsertFiscalYearQuarterOfYear = Table.AddColumn(InsertPeriodFyEnd, "Fiscal Year Quarter", each Date.QuarterOfYear(Date.AddMonths([Date], 6))) in InsertFiscalYearQuarterOfYearDashboard Template
A Power BI template featuring a heatmap as a calendar-formatted matrix and buttons that reference bookmarks for day, week, month, quarter, and year to adjust the date timeline selection and the X-axis date hierarchy on trend charts. It includes date filtering options based on timeframes and ageing. This template was developed for on-premises Power BI Report Server but also works in the Service. The test data was created by using https://mockaroo.com/ .DAX Custom 445 Calendar
Thanks to a request by dogt1225 from this thread comes this highly configurable DAX Custom 445 Calendar. Now, this is certainly not the first custom calendar nor will it be the last but it is one that I created and I think it is notable because of how easily configurable it is to customize for your own needs. In this instance, the calendar is configured for weeks starting on Saturday and ending on Friday starting on the 5th Saturday of the year 2020 for 2 years. Additional features of this calendar include assigning a week # of the year, week # of the quarter, sequential week #, quarter, month, day of year, etc. Custom445 = VAR __StartYear = 2020 // starting year VAR __NumYears = 2 // number of years including start year VAR __WeekForm = 16 // 16 has Saturday as 1, Friday 7 VAR __StartDay = 1 // weekday to start calendar on VAR __StartWeek = 5 // # instance of weekday to start calendar on (5th Saturday for example) VAR __Base = CALENDAR(DATE(__StartYear,1,1),DATE(__StartYear,12,31)) VAR __StartDate = MAXX( FILTER( ADDCOLUMNS( __Base, "WeekNum",COUNTROWS(FILTER(__Base,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay)) ), [WeekNum]=__StartWeek && WEEKDAY([Date],16)=__StartDay ), [Date] ) VAR __CalendarBase = CALENDAR(__StartDate,__StartDate + 52 * __NumYears * 7 - 1) VAR __Calendar = ADDCOLUMNS( ADDCOLUMNS( ADDCOLUMNS( ADDCOLUMNS( __CalendarBase, "Year",ROUNDUP(([Date]-__StartDate+1)*1./ (52*7),0)-1+__StartYear, "DAYOFWK#",MOD(([Date] - __StartDate),7)+1, "SEQWK#",COUNTROWS(FILTER(__CalendarBase,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay)), "DAY#YEAR",MOD(([Date]-__StartDate)*1.,(52*7))+1, "DAY",DAY([Date]) ), "WK#",ROUNDUP([DAY#YEAR]/7,0), "QWK#",MOD([SEQWK#]-1,13)+1, "Q",ROUNDUP([DAY#YEAR]/91,0) ), "Month",SWITCH(TRUE(), [Q]=1 && [QWK#]<=4,1, [Q]=1 && [QWK#]<=8,2, [Q]=1,3, [Q]=2 && [QWK#]<=4,4, [Q]=2 && [QWK#]<=8,5, [Q]=2,6, [Q]=3 && [QWK#]<=4,7, [Q]=3 && [QWK#]<=8,8, [Q]=3,9, [Q]=4 && [QWK#]<=4,10, [Q]=4 && [QWK#]<=8,11, [Q]=4,12 ) ), "MonthName", SWITCH([Month], 1,"February", 2,"March", 3,"April", 4,"May", 5,"June", 6,"July", 7,"August", 8,"September", 9,"October", 10,"November", 11,"December", 12,"January" ) ) RETURN __Calendar eyJrIjoiOTE4YTNmOWUtNTBjOS00ZjM3LWEzZjEtMTU3OTE2YjM5ZmFjIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
