Max Date with Additional Columns

Hi,

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_datalake iredm registration_model_view’[term_desc])
VAR CurrentID = SELECTEDVALUE(‘prod_datalake iredm registration_model_view’[entity_id])
VAR CurrentCRN = SELECTEDVALUE(‘prod_datalake iredm registration_model_view’[course_reference_number])
– used to be RETURN
VAR MaxDate =
MAXX(
FILTER(ALL(‘prod_datalake iredm registration_model_view’), ‘prod_datalake iredm registration_model_view’[term_desc] = CurrentTerm &&
prod_datalake iredm registration_model_view’[entity_id] = CurrentID &&
prod_datalake iredm registration_model_view’[course_reference_number] = CurrentCRN &&
prod_datalake iredm registration_model_view’[uncg_active_from_date] <= SelectedDate),
prod_datalake iredm registration_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 ,

You can try running your query here: https://mentor.enterprisedna.co/queries

Cheers,

Enterprise DNA Support Team

I need help crafting the DAX code, if it is even possible.

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
Ankit J
EDNA_MaxDate_Sol.pbix (24.1 KB)

Hi Ankit,

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.

Does this make sense?

Thanks

Dan

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.

image

Thanks
Ankit J

Ankit,

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?

Thanks
Dan

Ankit,

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.

Thanks Again,
Dan

Hi @d.chek - Yes, it needs to be applied on all visuals. If you need for entire page then can apply as a page level filter.

Regarding your second question, may need separate logic. I am not clear on requirement so share with some example and sample data.

Thanks
Ankit J

Hi @d.chek

Please check out the DataMentor and EDNA AI tools that is built in the EDNA platform that will be able to help you solve some of your questions.

Thanks
Keith