Forum Discussion
Show Data Between 2 Dynamic Dates
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:
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:
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!
