Forum Discussion
Show Data Between 2 Dynamic Dates
Hi All, I'm having an issue where I want users to select a reportiong period (date), and then select slicer that can filter the data to the last 1/3/6/12 months.
E.g. They select the date 31 Dec, 2023, and want to see the last 1 year of data so the date range shown in all visualisations on the page is 1 Jan 2023 - 31 Dec, 2023.
My data has the following structure/tables.
Table 1 Reporting Period Start End: A list of the start and end dates for each reporting period and data period
Table 2 Reporting Period Slicer: A reporting period table that users select (has relationship with Table 1):
Table 3 Data Period Slicer: A data period table that users select (has relationship with Table 1):
Table 4 Date Table: A date table with just a list of dates:
Table 5 Sum Table: A list of dates with numbers that I want to perform calculations on (dates can repeat) (has relationship with Table 4)
Initially I created a measure like the below:
Start Rep Period = selectedvalue('Reporting Period Start End'[Start Date])
and
End Rep Period = selectedvalue('Reporting Period Start End'[End date])
then created another measure
Within Range =
if(AND(selectedvalue('Sum Table'[Date]) >= 'Reporting Period Start End'[Start Rep Period Date], selectedvalue('Sum Table'[Date])<= 'Reporting Period Start End'[End Rep Period]), 1, 0)
then used this 'Within Range' measure on my visualisations and set the filter to 1 so it only included data from within that time range.
The thing is I have a lot of visualisations. I don't want to have to go through and add this to every visualisation. I was hoping that I could use the slicer visualisation and have it set to between 2 dates like this one
Then put the start date as 'Start Rep Period' measure and the end date as 'End Rep Period' measure but I don't know how to do that either. Does anyone have an easier solutions to this question?
One option is to create a calculation group with this logic, and then apply the calculation group logic via a filter at the visual, page, or all pages level. You would use the function SELECTEDMEASURE instead of SUM('Fact Table'[Amount]). This allows you to apply the logic to any measure in the visual. Here's an article on the topic:
4 Replies
Try this solution. Create the tables and relationships below. Table 2 in your example isn't needed. The Date table is a standard date table (one row per day).
Create slicer "Reporting Period" using 'Reporting Period'[Reporting Period]. This is a "Before" slicer.
Create slicer "Data Period" using 'Data Period Slicer'[Data Period].
Create matrix using 'Date'[Date] as rows.
Create measure:
Dynamic Sum = VAR vStartDate = SELECTEDVALUE ( 'Reporting Period'[Start Date] ) VAR vEndDate = SELECTEDVALUE ( 'Reporting Period'[End Date] ) VAR vResult = CALCULATE ( SUM ( 'Fact Table'[Amount] ), KEEPFILTERS ( 'Date'[Date] >= vStartDate ), KEEPFILTERS ( 'Date'[Date] <= vEndDate ) ) RETURN vResult-----
This is an awesome solution, it covers around half my visualisations with just one measure.
But with this I would have to write quite a few different measures which I'm not opposed to doing as I think it is better than the solution that I had.
In the interest of saving time, do you think this is possible to do this using a filter that goes on the 'Filters on this page' section of the Filter panel? Or there is a slicer on the page that is hidden that can affect all the visualisations?
I feel like it should be possible but at the same time I'm having a lot of issues coming up with the correct solution. I didn't want to add a filter to each individual visualisation using my original method and although your one is a lot better, it would still be a lot of additional measures.
One option is to create a calculation group with this logic, and then apply the calculation group logic via a filter at the visual, page, or all pages level. You would use the function SELECTEDMEASURE instead of SUM('Fact Table'[Amount]). This allows you to apply the logic to any measure in the visual. Here's an article on the topic:
