Show Data Between 2 Dynamic Dates

[alee5210]

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?

[DataInsights]

@alee5210,

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

-----

[alee5210]

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.

[DataInsights · Accepted solution]

@alee5210,

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:

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-model-explorer-with-calculation-group-au... 

[alee5210]

That is exactly what i'm after, you are a life saver. I had no idea that this feature existed, but it's a huge help!