Forum Discussion

kavster's avatar
kavster
Frequent Visitor
4 years ago
Solved

DAX LastDate? Problem

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; ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
    kavster
    4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago
  • hello, can you add a file pbix?
kavster's avatar
kavster
Frequent Visitor
4 years ago

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.