I have a Max Date calculation that returns the correct max date. Now I want to get additional columns from my base table for the max date that I have calculated.
Date Looks like this:
Term ID CRN Date Program
202408 1111 99999 4/11/2024 ABC
202408 1111 99999 4/16/2024 DEF
202408 1111 99999 4/24/2024 GHI
I have a date slicer that the user can enter a date. If they select 4/24/24 then program GHI is returned, if they select date 4/23/24 then program DEF is returned. Because I calculate the max date that is <= prompted date. This works great for the max date and this is my code.
Max Date DNA =
– Slicer Date
VAR SelectedDate = SELECTEDVALUE(‘Date Table’[Date])
– Grouping of rows where multiple dates for Term / ID / CRN
VAR CurrentTerm = SELECTEDVALUE(‘prod_datalakeiredmregistration_model_view’[term_desc])
VAR CurrentID = SELECTEDVALUE(‘prod_datalakeiredmregistration_model_view’[entity_id])
VAR CurrentCRN = SELECTEDVALUE(‘prod_datalakeiredmregistration_model_view’[course_reference_number])
– used to be RETURN
VAR MaxDate =
MAXX(
FILTER(ALL(‘prod_datalakeiredmregistration_model_view’), ‘prod_datalakeiredmregistration_model_view’[term_desc] = CurrentTerm &&
‘prod_datalakeiredmregistration_model_view’[entity_id] = CurrentID &&
‘prod_datalakeiredmregistration_model_view’[course_reference_number] = CurrentCRN &&
‘prod_datalakeiredmregistration_model_view’[uncg_active_from_date] <= SelectedDate),
‘prod_datalakeiredmregistration_model_view’[uncg_active_from_date])
RETURN MaxDate
I know simply want to get any additional columns, like Program) for my base table for the max dated row. Further, I want to display this information in a table visual.
Any help is greatly appreciated, or if you need additional information.
Thanks
Hi @d.chek - Please check the attached Solution. I have tried to replicate your data and Scenario. I have created one more measure as below and applied as filter on the Table Visual. This will keep only those records that are equal to Max Date and Exclude other records from visual.`
KeepRecords = if(SELECTEDVALUE(Test[Date]) = [Max Date DNA],1,0)
Thanks so much for the response. This works to a point. It provides my max dated row for the selected date, however, my data contains values that change. So in your example, I have a code field that changes. On April 11th it contains ZZZ and on April 15th it contains YYY. If I select date of April 11th, it should locate the max dated row <= April 11th and the code should display ZZZ. Likewise, If i select date April 15th and that is the most recent change (max date <= selected dated), then it should display YYY. As long as I don’t display the code field, I get a single row, however when I add the code field (the field that changed), I get two rows on my table showing April 11th code ZZZ and April 15th code YYY.
So basically, once I get the max dated row <= selected date I also want to display any other fields for that selected row.
Hi @d.chek - Can you share with an example and sample dataset. In my Solution, I have taken below data.
Term
ID
CRN
Date
Program
202408
1111
99999
Wednesday, April 24, 2024
GHI
202408
1111
99999
Tuesday, April 16, 2024
DEF
202408
1111
99999
Thursday, April 11, 2024
ABC
Here Max logic is applied on all columns except Program. So even if I add program in table visual and select date as 23rd April, I am getting a single Row for 16th April.
Everything seems to be working OK, I will test it out more by pulling fields from other tables and such. I had your Max Date DNA logic but didn’t have the KeepRecords measure. Can you explain the KeepRecords measure in a bit more detail? Will the KeepRecords measure need to be a filter on every visual I create?
How would I incorporate this logic for a summary matrix? Say I want a count of Programs, but want the count as of the specific date (max date). I’m trying to do point in time reporting, so I’m getting counts on a specific day. If a record exists, then I just need to count that record once based on the max date <= selected date. If no record exists, then its not counted.
