#Bi fact table timeslice update
#Bi fact table timeslice code

In this case, drag the column from the ‘Date’ table to a blank area of canvas and set the visual to be a slicer. This example uses a simple SUM over the column. Then add measures to Values section of the visual. The calculated measure is added to the filter pane and set that it must have a value of 1. The key features of the report are that the visual uses the column from the ‘Data Table’ and not the month column from ‘Dates’. The DATEADD function can only work with a column reference and cannot use the output of a MAX function. The DateAddAlternative variable uses the EDATE function to perform a DATEADD like jump back in months using the MonthsToLookBack variable to determine the range. If the user selects January 2017 from the slicer, it will be this value that gets stored in this variable.

The MIN function is used to guarantee a single date value. The DateTableDate variable will store the value selected from the Slicer. This will effectively control which rows from the ‘Data Table’ table will be used by the calculated measures on the visual. The DataTableDate variable will store the minimum value of the field from the ‘Data Table’ for each bucket on the axis. This variable could be substituted with a What-If parameter if you’d like to provide more flexibility to your end users. The MonthsToLookBack variable stores a value that will be used to control how many months back from the slicer selection will be used for the date range.

#Bi fact table timeslice code

VAR DateAddAlternative = EDATE(DateTableDate,-MonthsToLookBack)Ī breakdown of the code in the calculated measures is as follows: This measure can be added to either table. Add the following calculated measure to the model. The final piece of code is to create a calculated measure based filter that will be used to help select the date range. Power BI Desktop may attempt to link the two tables, so delete the relationship if this happens. The important thing is to make sure there is no relationship between these two tables. This code can be tweaked a number of ways to control when the data table starts and finishes, how many rows per day, as well as the parameters for the random number used for. This is the column we will use in the axis of a visual.

The third column is the start of the month value relevant for each. It generates a random number between 1 and 5 in each row for the column. This calculated table will generate a table with 2 sales per day between 1 January 2012 and the current day. SELECTCOLUMNS(GENERATESERIES(1,2),'Units Sold',RANDBETWEEN(1,5)) Table 2 : This table will generate some fake sales to be visualised. If you cut and paste this code into a blank Power BI Desktop file as a New Table, this will give you a table called ‘Dates’. Table 1 : This will generate a date table that we can use for the slicer. Lets generate two tables of dummy data to demonstrate. The same is true when using a field for the slicer from a related table.Ī way to solve this is to use a field for the slicer from a table that has no relationship with the data table. The challenge is if the “Month” slicer uses a field from the data table used to generate values for the visuals it will restrict the rows from the data table available to generate the axis, that no amount of fiddling with row or filter context can over-ride.

#Bi fact table timeslice update

If a user then selects December 2016 on the slicer, visuals will update to show data for a date range between March 2016 and December 2016. If a user selects March 2017, visuals will display a date range between May 2016 and March 2017. A recent post on a forum asked if it was possible to show a slicer of months, and configure a report so when a user selects a single month from the slicer, visuals will show data for a date range relative to that selection e.g.