Hi,
I have attached sample data which shows a source, ID and date columns. https://docs.google.com/spreadsheets/d/14NvDRXJmDQYiBoAQBrqOr19kbwrGEzYQ/edit#gid=1163101435
On my dashboard I have an imported visual, timeline slicer (timeline 2.4.0) which will allow the user to select different periods of data. E.g. if the timeline slicer has been selected as August 2022, the data will be between 01/08/2022 - 31/08/2022.
However the measures below should ignore the slicer and only take into account the last date selected by the user. The calculation below should ignore the 01/08/2022 and instead filter the dataset down to date <= 31/08/2022.
The dashboard allows the user to also select a cut-off date.
Assuming the last date selected is 31/08/2022, the first step is for the sample data attached to filter down to only show rows where the date is <= 31/08/2022.
Of those rows, there is then another 'cut-off' date selected by the user. This could be 01/08/2022.
Therefore, I need the MAX date per ID from the filtered data, where the data is <= 31/08/2022.
For ID 123 that would be 23/08/2022
For ID 456 that would be 25/08/2022
For ID 789 that would be 28/07/2022
From that filtered data, the max date should be compared to the cut-off date, 01/08/2022. If the date is before the cut off date then I would like to count the distinct ID's which match that criteria. In this case, only 789, so the result is 1.
The approach I have taken so far is (To keep things simple I have hardcoded the date for this question):
var datefiltered = FILTER(table1, table1[Date] <= DATE(2022,08,31) && table1[source] IN {"Runs"})
This gives me a filtered table which match the criteria I want.
var maxdatetable =
DISTINCT(
SELECTCOLUMNS(filteredtable,
"ID", table1[ID],
"Date2", table1[Date]))
The idea here is to take that filtered table and then get the max date per app id using max and allexcept but it does not work.
I've tried many variations including wrapping a calculatetable / trying to use my filtered table as a filter in a calculatetable expression but can't seem to get it to work.
I have got this working using a disconnected table, e.g. not connected to the dates table I currently have which is made from a CALENDARAUTO() function. But, I need it to work without disconnecting as it is cleaner and better for user filtering.
Would greatly appreciate any help the community can provide!
Thank you!
