topic Re: DAX LastDate? Problem in DAX Commands and Tips

DAX LastDate? Problem

kavster — Sun, 13 Mar 2022 22:45:33 GMT

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.

Re: DAX LastDate? Problem

Anonymous — Sun, 13 Mar 2022 23:38:26 GMT

hello, can you add a file pbix?

Re: DAX LastDate? Problem

kavster — Tue, 15 Mar 2022 10:21:50 GMT

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.

Re: DAX LastDate? Problem

Anonymous — Tue, 15 Mar 2022 13:51:32 GMT

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 😁

Re: DAX LastDate? Problem

kavster — Tue, 15 Mar 2022 14:09:23 GMT

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