Dynamic change in X Axis for date formatted as text

[acanepa]

Hello,

I'm trying to build a dashboard where I can define time granularity but also a filter on the X axis, where filter last month data or similar from a slicer. I have defined a calendar table and a Date granularity table (as the following image). Also, a "slicer" table called DateFilters containing text and ID, for selecting Last Month, Last Week and so on.

Here is the detail of each of the two tables.

My idea is to generate a line chart where I can choose the time granularity but also being able to filter last week data only with a slicer. My approach to this is with the next calculation

I have created a calculated column called FilterDateText

FilterDateText = SWITCH(HASONEVALUE(DateFilters[Date]),
	max(DateFilters[id])=103,if([DateKey]<=TODAY() && DateTimeGranularity[DateKey]>=STARTOFMONTH(DateTimeGranularity[Today]),VALUES(DateTimeGranularity[DateText]),BLANK()))

id 103 relate to Last Month from the slicer table.

Creation of the table.

DateTimeGranularity = 
var day_table =  ADDCOLUMNS(DISTINCT('Calendar'[date]) ,"TimeGranularity","Day","DateText",day([Date])&"/"&month([Date])&"/"&year([Date]))
var month_table =  ADDCOLUMNS(DISTINCT('Calendar'[date]) ,"TimeGranularity","Month","DateText",FORMAT([Date],"mmm")&"-"&year([Date]))

var quarter_table = ADDCOLUMNS(DISTINCT('Calendar'[date]) ,"TimeGranularity","Quarter","DateText", "Q"&ROUNDUP((MONTH([Date])/3),0)&"-"&year([Date]))
var year_table = ADDCOLUMNS(DISTINCT('Calendar'[date]) ,"TimeGranularity","Year","DateText",year([Date]))
return(
union(day_table,week_table,month_table,quarter_table,year_table)
)

[bblais]

Check out Chris Webb's Blog for an idea:

Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Filtering A...

[acanepa · Accepted solution]

Great!. It seems I need to flag each row, and with that, I use as a filter, similar to what I have done already for Time Granularity. I will post my solution for documentation purposes.

EDIT:

I have created a table on the fly for creating the connection between tables. Basically making an union between the central Date or calendar table and the table I create for identify Last Month, Last Week and so on. Code attached:

Table = 
var thisweek = today()-WEEKDAY(TODAY(),2)+1
var thismonth= EOMONTH(today(),-1)+1
var t1= date(year(TODAY()),"01","01")
var thisyear= CALENDAR(t1,today())
var lastweek1 = today()-WEEKDAY(TODAY(),2)-6
var lastweek2 = lastweek1+6
var lastmonth1 = EOMONTH(today(),-2)+1
var lastmonth2 = EOMONTH(today(),-1)

var lastyear2= t1-1
var lastyear1= DATE(YEAR(lastyear2),"12","31")

return(union(ADDCOLUMNS(CALENDAR(thisweek,TODAY()),"DateFilter","This Week"),ADDCOLUMNS(CALENDAR(thismonth,TODAY()),"DateFilter","This Month"),ADDCOLUMNS(thisyear,"DateFilter","This Year"),ADDCOLUMNS(CALENDAR(lastweek1,lastweek2),"DateFilter","Last Week"),ADDCOLUMNS(CALENDAR(lastmonth1,lastmonth2),"DateFilter","Last Month"),ADDCOLUMNS(CALENDAR(lastyear1,lastyear2),"DateFilter","Last Year")))

Regards,