DAX LastDate? Problem

[kavster]

Hi, I wonder if someone can help me get past this road block.  I have tried so many options (mainly around virtual tables and dax last date) I think my approach must be wrong.

I have two tables; the first a date table (below left) with a column showing the period and year that each date falls into (13 financial periods of 4 weeks).  

In the second table (below right) I have the same financial period format column; I am trying to build a calculated column in this second table that returns the last date only of each period using the date table.  No relationship between tables, multiple values in FY-Period columns of both tables (date column is unique).  

Thank you.

[Anonymous · Accepted solution]

hi @kavster 

I think the best way is to first create a summarized table : 

last_date_table_period = SUMMARIZE(
'Periods and Weeks','Periods and Weeks'[FY-Period],"last_date", LASTDATE('Periods and Weeks'[Date]))

after that you add a coulmn in the full_schedule_data : 

last_date = LOOKUPVALUE(
last_date_table_period[last_date],last_date_table_period[FY-Period],Full_Schedule_Data[Period_FY]
)

Did I answer your question? Thanks to mark my post as a solution 😁

[Anonymous · Accepted solution]

hi @kavster 

I think the best way is to first create a summarized table : 

last_date_table_period = SUMMARIZE(
'Periods and Weeks','Periods and Weeks'[FY-Period],"last_date", LASTDATE('Periods and Weeks'[Date]))

after that you add a coulmn in the full_schedule_data : 

last_date = LOOKUPVALUE(
last_date_table_period[last_date],last_date_table_period[FY-Period],Full_Schedule_Data[Period_FY]
)

Did I answer your question? Thanks to mark my post as a solution 😁

[kavster · Accepted solution]

Yep, you nailed it.  Brilliant thank you for the help, works perfectly. 

[Anonymous]

• hello, can you add a file pbix?

[kavster]

PBIX file at link below.

https://drive.google.com/file/d/1kIGdn4PguMTHg7LBqL9G-H0jP9RpxmV-/view?usp=sharing

What I am trying to achieve is a new calculated column in the Full_Dchedule_Data_Table by looking up the Period_FY date (blue box) in the Period and Weeks Table (red box) and returing the Date (green box).  But it need to be the latest date in that period.  So for example P11_2021 would return a date in the calculated column of 05 Feb 2022, which would be on all rows where the FY_Period is P11_2021.