retrieve value from date table based on date in current table

[jtpiazzamn]

Date table has the following fields: (Date field lists each day in the year)

Date	Start of Month
1-1-2023	1-1-2023
1-2-2023	1-1-2023
3-1-2023	3-1-2023

Members table has the following fields (has more but for the context of this)

Created date	Month-Year
1-10-2023
1-2-2023
3-1-2023

I want to add a column to the Members table (named:Month/Year) using the value in "Start of Month" value in the Date table which is equal to the "created date" value in the Members table. 

I know -probably basic but haven't used DAX in a while

Thanks!

Jim

[Greg_Deckler · Accepted solution]

@jtpiazzamn Try:

Month/Year Column =
  VAR __CreateDate = [Created date]
  VAR __Result = MAXX(FILTER('Date',[Date] = __CreateDate), [Start of Month])
RETURN
  __Result

[jtpiazzamn]

Thank you - I forgot to mention that the relationship between the Date Table and the Members Table is "inactive".

[FreemanZ]

hi @jtpiazzamn 

Greg's code does not rely on relationship. what issue did you encounter?

[jtpiazzamn]

When I first started with a table 

I then changed it to a bar chart 

Then it worked - odd. 

[jtpiazzamn]

It appears to be ok. I thought it was relevant. 
I have another question though. When I go to use this column (formatted as Date), when using it in a chart, it reads it as a count only - can't display the value of the date in the column. 

I'm confused. 

Thank you for helping!

Jim