Let’s say that one of the top requirements for a new Power BI report and dashboard is to provide a snapshot of the prior weekday. The users want to start their day off with a look at performance for the previous weekday in the Power BI service or possibly receive a daily email subscription.
Example Use Case
As one example, imagine a manufacturing facility in which the business users have a good idea of daily production results (e.g. units produced, unit failures, etc). A quick glance at these daily metrics via the Power BI service or the Power BI mobile apps could advise the user that, at least for the day, operations are normal. Alternatively, perhaps something happened or changed significantly and this unexpected result deserves further analysis. (Data alerts configured on Cards, KPIs, or Gauge dashboard tiles in the Power BI service could be the natural fit for this scenario as the alert notification email eliminates the need to access the Power BI web service or mobile apps to view and analyze the numbers)
The Filter Requirement: Prior Weekday
On a Monday morning, the Power BI content (report, dashboard) should reflect data from the previous Friday. Likewise, if anyone accesses the content on Saturday or Sunday, the content should also reflect the latest Friday. On all other days (Tuesday through Friday), the Power BI content should be filtered for the previous day. For example, on Tuesday the content should reflect the data from yesterday (Monday).
Implementation Options
This requirement is not complex by any means but it’s also not something you can define via Power BI’s relative date filter conditions either. You have to implement this logic at some level of the solution or overall BI architecture such as the following:
- Add a column to the date dimension table and create or modify a process that updates this column nightly.
- A SQL UPDATE statement could be included in a stored procedure or an ETL package.
- The new column would contain a Boolean (True/False) value to identify the date row for the prior weekday.
- Modify the SQL View object for the date dimension to derive this column’s value (True/False) at the time the data model is refreshed or, in the case of DirectQuery models, accessed by queries.
- This assumes you are following the best practice of using a layer of SQL views (one view per table) as your source for Power BI and Analysis Services models rather than accessing the tables directly.
- Modify the Power Query (M) query used to load the date dimension table to the model to derive this column’s value (True/False).
- Add a DAX Calculated Column to the date dimension table that computes a True/False value at the time the date table is process/refreshed.
- Finally, you could avoid adding a column altogether by building dynamic filter logic into DAX measures.
The decision is….it depends
My general ‘philosophy’ is that the depth of the implementation, in terms of the layer at which the logic should be applied, should be driven by reusability and confidence in the requirement. For example, if I know there are many reports or other use cases that will take advantage of this new column and we’re 100% sure that the requirement is well understood and will not change anytime soon then I’d be looking to build this code/logic into the data warehouse. At the other extreme, such as when only one report needs this logic and/or there’s little confidence that the business rule will be sustained, then I’d be looking at more of a lightweight option such as Power Query (M), at least in the short term.
Of course other factors such as resource availability, complexity of the logic/rule, timelines, and company policies all come into play which underscores “it depends”. It wouldn’t be difficult to describe a realistic scenario in which any one of the 5 options mentioned could be the best or perhaps only option available. However, as I consistently mention, a basic understanding of Power Query (M) eliminates the need for DAX calculated columns in most cases so I’d look at calculated DAX columns only after both SQL and Power Query (M) are ruled out. Furthermore, I’d generally recommend centralizing the logic in one transformation ‘layer’ such as the SQL views or Power Query rather than handling some transforms in SQL, others in M, and still others in DAX. (I mentioned this as #12 of the Data Model Anti-Patterns)
SQL View Example
The following approach uses a Common Table Expression(CTE) against a SQL Server database to compute a Prior Weekday Flag column:
WITH PriorWeekDayCTE (PriorWeekdayDate) AS ( SELECT CASE WHEN DATENAME(WEEKDAY,CAST(CURRENT_TIMESTAMP AS date)) = 'Sunday' THEN DATEADD(DAY,-2,CAST(CURRENT_TIMESTAMP AS date)) WHEN DATENAME(WEEKDAY,CAST(CURRENT_TIMESTAMP AS date)) = 'Monday' THEN DATEADD(DAY,-3,CAST(CURRENT_TIMESTAMP AS date)) ELSE DATEADD(DAY,-1,CAST(CURRENT_TIMESTAMP AS date)) END AS [PriorWeekdayDate] ) SELECT D.[Date] , CASE WHEN D.[Date] = (SELECT PriorWeekdayDate FROM PriorWeekDayCTE) THEN 'TRUE' ELSE 'FALSE' END AS [Prior Weekday Flag] FROM DBO.DimFinDate as D
With the column now part of the SQL view and thus dynamically computed with each data refresh of the date table dimension, the column can be used as a filter condition (report level, page level, visual level) in Power BI reports. (The actual SQL view would of course retrieve many other date columns)
In the following example, the Prior Weekday Flag column is applied as a page level filter:
Assuming all metrics can be filtered via the relationship of the fact table to the date table, the new column makes it easy to build the visuals which will always reflect the prior weekday per the requirements (ie show Friday on Monday, etc).
Power Query (M) Example
Maybe the SQL view isn’t an option or maybe Power Query (M) is preferable to the BI developer. The following M expression adds the same column as the SQL view via Power Query (M) variables and functions:
let
CurrentDate = DateTime.Date(DateTime.LocalNow() + #duration(0,-4,0,0)),
PriorWeekDate =
if Date.DayOfWeekName(CurrentDate) = “Sunday” then Date.AddDays(CurrentDate,-2)
else if Date.DayOfWeekName(CurrentDate) = “Monday” then Date.AddDays(CurrentDate,-3)
else Date.AddDays(CurrentDate,-1),
Source = AdWorksSQLServer,
FinDate = Source{[Schema = “BI”, Item = “vDim_FinDate”]}[Data],
PriorWeekDayFlag = Table.AddColumn(FinDate, “Prior Weekday Flag 2”, each
if [Date] = PriorWeekDate then “TRUE”
else “FALSE”, type text)
in
PriorWeekDayFlag
In this example, the CurrentDate variable adjusts for the four hour offset between EDT (Boston, USA) and UTC and then passes this date to the PriorWeekDate variable’s conditional logic (if Sunday….this..if Monday..that) to compute this date. Finally, just like the SQL view, the computed date is compared to the date column to produce a “Prior Weekday Flag 2” column. (A unique column name is required given the Prior Weekday Flag column that was added to the vDim_Findate view in the SQL example.)
The above Power Query (M) expression (the Date query) is fully folded into a single SQL statement for processing by the SQL Server database per the following image:
As this is just a date table with a few thousand rows a fully folded query isn’t essential. Nonetheless, just like when you write DAX measures, it’s a good habit to go beyond the mere functional requirements to also consider resource utilization and performance.
DAX Measure Example
Let’s say that for some reason you can’t add a new column to the date dimension table by any means – SQL, M, or DAX calculated column. Per implementation option #5 mentioned earlier, you could build the logic directly into DAX measures.
Since your prior weekday reports likely involve several measures, I would create a measure which computes the prior weekday and then leverage this measure as a filter condition for other new measures per the following example:
Prior Weekday = VAR NowAdjusted = NOW() - (4/24) VAR TodayAdjusted = DATE(YEAR(NowAdjusted),MONTH(NowAdjusted),DAY(NowAdjusted)) VAR CurrentWeekday = WEEKDAY(TodayAdjusted,1) RETURN SWITCH( CurrentWeekday, 1,TodayAdjusted - 2, 2,TodayAdjusted - 3, TodayAdjusted - 1) Net Sales (Prior Weekday) = CALCULATE([Internet Net Sales],FILTER(ALL('Date'), 'Date'[Date] = [Prior Weekday]))
The Prior Weekday measure adjusts for the four hour offset to UTC by subtracting a decimal (.1666667 or 4/24) from NOW(). This DAX variable is then used as the input to YEAR(), MONTH(), and DAY() functions to produce the TodayAdjusted variable.
The WEEKDAY() function returns an integer corresponding to the current day (e.g. 2 for Tuesday) and the SWITCH() function evaluates this value to either back up 1, 2, or 3 days from the current date.
The Prior Weekday measure could be assigned to a table in the data model which would be hidden from the user interface. I usually use the name ‘Measure Support’ for this hidden table. I’ve heard rumors from a reliable source, however, that something like global DAX variables could be released later this year.
The Net Sales (Prior Weekday) simply filters an existing measure (Internet Net Sales) by the Prior Weekday measure. When filtering by a measure, you of course need to use FILTER() and, similar to date intelligence patterns, pass an unfiltered Date table as the input to FILTER via ALL(). Each measure (ie sales, orders, units, etc) required would need to have its own prior weekday version.
Wrapping Up
If the report consumer or primary stakeholder effectively says something like “I need to see the prior weekday” (e.g. see Friday on Monday) or “I’m not interested in weekends” you have several options available, only a few of which are highlighted in this post. Additionally there are other variations of the three examples shared. At a minimum, hopefully this post gives you a few ideas on the specific scenario (prior weekday) and a few thoughts on the broader considerations (SQL vs. M vs. DAX).
The Business Applications Summit will surely offer significant announcements and updates (e.g. July PBI Desktop features) so my next post may well review some of the headlines and what was (or wasn’t) said or suggested in terms of roadmap.
Insight Quest 