Forum Discussion

jtpiazzamn's avatar
jtpiazzamn
Helper I
3 years ago
Solved

retrieve value from date table based on date in current table

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

DateStart of Month 
1-1-20231-1-2023 
1-2-20231-1-2023 
3-1-20233-1-2023 

 

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

Created dateMonth-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

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    jtpiazzamn Try:

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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    jtpiazzamn Try:

    Month/Year Column =
  VAR __CreateDate = [Created date]
  VAR __Result = MAXX(FILTER('Date',[Date] = __CreateDate), [Start of Month])
RETURN
  __Result
    • jtpiazzamn's avatar
      jtpiazzamn
      Helper I
      3 years ago

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