Hello all i am trying to get a subset of data from another table using a selected date from a slicer.
Ideally what i want is, if a date is selected, i want to get all records from the last 7 days to the selected date.
I am using the DAX formula below;
OutputTable =
var dateSelected =CALCULATE( max(SelectedDate[Date]),ALL(Dates))
var last7days = DATEADD(SelectedDate[Date], - 7,DAY)
VAR Table2 = SUMMARIZECOLUMNS(JiraData[Issue key],
FILTER(All(JiraData), JiraData[Resolved] > last7days && JiraData[Resolved] <= dateSelected ))
RETURN FILTER(JiraData, JiraData[Issue key] IN Table2)
Notes
The table to filter is JiraData
A list of all IssueKeys where the Resolve date is between the last7Days and DateSelected are put into variable Table2
We then Filter JiraData using the IssueKeys in Table2 to return another table containing only the rows that match Issue Keys using the IN function.
Although i have dates that fall within the Resolve date column, I am unable to get this work i.e. unable to create the sub table. Any help/clues will be greatly appreciated.
Thanks in advance for this very interesting post.
I didn’t quite understand point 3 of your observations,
“3. We then Filter JiraData using the IssueKeys in Table2 to return another table containing only the rows that match Issue Keys using the IN function.” whereas, table2 is a summary of the JiraData table.
The code below does not work because you are comparing data from a column with a table. find the link to the IN function.
I ask to try the ideas below.
Idea 1
OutputTable =
Var SelectedDate =
MAX( ‘Date’[Date] )
