Relationships for Relative Date Filtering

[Anonymous]

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?

[amitchandak · Accepted solution]

@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 

[amitchandak · Accepted solution]

@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 

[Anonymous]

This generates an error (same one I always recieve) "Cannot convert value '01--' of type Text to type Date.

[v-xicai · Accepted solution]

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.

[Daviejoe]

This might help too

https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

[Anonymous]

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.

[Daviejoe]

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