Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Relationships for Relative Date Filtering

I am trying to create a relationship between table A (has a date column) and table B (has a year column and a month column both  are in TEXT format and cannot be formated as date). Visuals on a page ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous ,

    You can create a date like

     

    Date = date([year], [month],1)

     or

    Date = "01-" & [Month] & "-" &[Year] // change format Date if Month is format Jan or January 

  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    If the 'Table B'[Month]  is like format "January, February ,,,",  you may use SWITCH function to change the value data format, then create calculated column [Date] like DAX below, change the data type to Date.

     

    'Table B' [Month] = SWITCH('Table B' [Month] ,"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)



Date=  'Table B' [Month] & "/01/" & 'Table B' [Year]

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Table B is used to capture end of month data by year and facility. So, I have three years, 12 months per year, and eight facilities. The format of YYYY-MM does not create unique values and neither did a third table I created with unique values 2018 - 2020 as YYYY-MM.