Forum Discussion
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 pull from tables A and B. I need a to establish a date relationship so I can set a relative date page filter to show last months data all of the time. I have tried several things, but to no avial. Does anyone has any suggestions?
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
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.
6 Replies
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
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.
Are you not able, in Query Editor to convert the year and month into a date format?
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
