Dynamic dating diagram

A way to solve this is to use a field for the slicer from a table that has no relationship with the data table.

Lets generate two tables of dummy data to demonstrate.

Table 1 : This will generate a date table that we can use for the slicer.

This calculated table will generate a table with 2 sales per day between 1 January 2012 and the current day.

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.

The same is true when using a field for the slicer from a related table.

The important thing is to make sure there is no relationship between these two tables. Measure as filter = VAR Months To Look Back = 10 VAR Data Table Date = MIN('Data Table'[Date]) VAR Date Table Date = MIN('Dates'[Date]) VAR Date Add Alternative = EDATE(Date Table Date,-Months To Look Back) RETURN IF( (Data Table Date A breakdown of the code in the calculated measures is as follows: The Months To Look Back 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.

Back to the top On-demand mapping allows engineers to create a network diagram customized to the task at-hand.The [Measure as filter] calculated measure is added to the filter pane and set that it must have a value of 1. This example uses a simple SUM over the [Units Sold] column. In this case, drag the [Month] column from the ‘Date’ table to a blank area of canvas and set the visual to be a slicer.In my version of Power BI Desktop I have set the slicer to be a List and formatted the [Month] column from the Date Table using the Format option in the Modeling table to set the field to be (MMMM yyyy).It generates a random number between 1 and 5 in each row for the [Units Sold] column.The third column [Month] is the start of the month value relevant for each [Date].

Leave a Reply