We are looking at a Star Schema with a Date table connected to the Fact table by Order date Key
for these examples, the active relationship is the only one that matters
Lets have a look at all the issues arising from using the Date table without marking at a Date table
Date Slicer seems to remove the report filter flag for the last 3 years
Rolling 3 year flag= IF(DATEDIFF('Date'[Date].[Date],TODAY(),YEAR)<=3 && DATEDIFF('Date'[Date].[Date],TODAY(),YEAR)>=0,1,0)
This flag has been added to the entire report and set to 1 to make sure there is only information from these 3 years
If I add date as a table I get 2017 2018 2019 and 2020 as expected for a rolling flag.
However As soon as I change that table into a Slicer, all the years appear in the data set.
In this case from 2016. The filter stops being applied on the slicer
DAX FUNCTION SAMEPERIODLASTYEAR and the date hierarchy
We have some DAX to create SAMEPERIODLASTYEAR.
This is the DAX
LY Total = CALCULATE([Total],SAMEPERIODLASTYEAR('Date'[Date].[Date]))
Note the .[Date] at the end. Because we are using the date hierarchy to create the measure you can choose which level of the hierarchy to use. In this case we are saying we want the date level.
This will come back later
This is used in a visual and we have added Month Year which is a column in the date table.
As you can see, last year displays exactly the same as the current measure. This does not work
it works using the DateTime Hierarchy in the visual? However in this instance this date time hierarchy isn’t required. We don’t want the user to have to drill down to month from year every time.
In this example, the only field you can use for any measure that is date time specific is the date field
Mark as Date time
Always make sure you have a date table connected to a fact table
Note that the Date is now not connected to a back end hierarchy table.
Marking as Date table means that all your data within this table can be used for Date Measures
However lets look at the DAX that was created
All the Lat year measures are now erroring. this is because of the .[Date] at the end of the DAX
The date does not contain a hierarchy any more so if you have used .[Date] This needs removing. Its specifically related to the hierarchy.
A Year to day hierarchy has been created in the date table. This means that you have created a more compact data model and saved space.
And you can now use Month Year on the Axis
Date Table is Marked. How to work with it
This is part of the star schema in Power BI
Now the date table is marked as date, the Date hierarchy table is removed for Date within the Date table. this saves space and you can simply create your own date hierarchy to work with
The Active Join is on Received Date
All my measures so far are based on received date
Each Date in the fact table creates an extra date table to produce the hierarchy. So to save space, you should create inactive joins to the other dates and then remove the dates in the Fact table leaving just the keys. the model should then reduce in size
This works great for the measures. I can create measures based on the none active ones and simply choose to USERELATIONSHIP
LY Totals by Closed Date = CALCULATE([Total],USERELATIONSHIP('Complaint Metrics'[Closed Date Key],'Date'[Date Key]),SAMEPERIODLASTYEAR('Date'[Date]))
The above is an example of using the Closed Date None active join for Last years Totals
So i can have measures for Received this Year, Received Last year and Closed this year , Closed Last year (For Example)
This is all absolutely spot on. However there is more logic we need to think about. What about when the users want to create a drill through
This visual was created on the active relatioship so its recieved date
However your users may want to drill through to the following
how to do this?
Currently you may have the Active join on received date and Inactive on Start and Closed date
In my example, I have also kept the dates in the fact table along with the Date Keys for each Date.
Because they are in the fact table they all have date hierarchies.
Remove the Date Hierarchies across your entire dataset
You could keep them there and simply remove their hierarchies
File > Options and Settings > Options
You can turn off time intelligence for the specific report OR globally. the recommendation is to actually do this globally when you use a date table
This way you can use the dates in the fact table for drill down without creating unnecessary data with the date hierarchies
Role Play dimensions
If you want to drill down to all your dates on a regular basis, AND if there isn’t one main date and other dates that are not used as often.
In Power Query Editor go to your date table and create a reference table for each date
In this case the Date table has been referenced once and is named date created. date as been renamed to date Engagement
with this set up, there is only one join to each date key so no inactive relationships.
Any DAX you create can reference the correct date tables and your Dates can be removed from the fact table
the big downside is you have a much more complex schema with many more dimensions so only go for this if your dates are all frequently used.
In the example above, the user just wants to drill through and see all the dates so they can be left in the fact table in a Date Folder without their hierarchies
But this has been a really useful bit of research on marking the date table, Date Hierarchies and role playing