Reply
staceyrain
Frequent Visitor

Dynamic Date Axis

I have seen lots of tutorials on adjust charts based on slicers, but I'd like to use one chart page for several projects and have the x-axis change the start and end date accordingly when someone selects a project.  I don't feel the end user should have to go select the appropriate date range. 

 

I am connected to a PWA report server, so I have a project table and task table and have a query that is filtered down to the tasks I have graphed in a burn-down chart.  I have created a date table that shows the w/e Friday dates for all weeks between the first basline finish date and the last baseline finish date.  Unfortunately these vary widely between projects and I can't get the chart to change the dates on the axis based on the project I've selected.  Has anyone seen a tutorial on this?  I've even got a table that grouped all the projects to show the first w/e date and last w/e date for each project, but can't figure out how to use that as a filter against the date table I created.

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@staceyrain 

 

You may add an appropriate measure to Visual level filters.

https://community.powerbi.com/t5/Desktop/Create-Axis-values-as-slicer/m-p/587458#M278489

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@staceyrain 

 

You may add an appropriate measure to Visual level filters.

https://community.powerbi.com/t5/Desktop/Create-Axis-values-as-slicer/m-p/587458#M278489

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

That might be the solution, but I sure couldn't make it work.  I tried a few different methods and can't get the dates to filter based on the project selected.  I have a table that shows the project name and the minimum and maximum dates that should be on the chart (CRMinMaxFinDates) and created this measure, but that's a list and won't work as a filter, so I did the same thing, making it a dynamic table and tried to use the column from that table as the X-Axis, but it doesn't filter when I select the project.

FilteredDates =
VAR Date1 = LOOKUPVALUE(CRMinMaxFinDates[MinDate],CRMinMaxFinDates[PROJECTID],SELECTEDVALUE(Tasks[Projects.PROJECTID]))-7
VAR Date2 = LOOKUPVALUE(CRMinMaxFinDates[MaxDate],CRMinMaxFinDates[PROJECTID],SELECTEDVALUE(Tasks[Projects.PROJECTID]))
RETURN DATESBETWEEN(WEDates[WEDate],Date1,Date2)
 
I did also try your method of creating an IF measure to return a 1 or 0 but when I add it as a filter and select only the 1's the chart has no dates and I suspect my MIN MAX functions are not correct, but I can't figure out how to refrence the dates on the X-Axis.
DateCheck =
VAR Date1 = LOOKUPVALUE(CRMinMaxFinDates[MinDate],CRMinMaxFinDates[PROJECTID],SELECTEDVALUE(Tasks[Projects.PROJECTID]))-7
VAR Date2 = LOOKUPVALUE(CRMinMaxFinDates[MaxDate],CRMinMaxFinDates[PROJECTID],SELECTEDVALUE(Tasks[Projects.PROJECTID]))
VAR FlagDateCheck =
IF(
MIN(DateTableQuery[WeekEndDate])<Date1
|| MAX(DateTableQuery[WeekEndDate])>Date2,
0,1)
RETURN FlagDateCheck

I got it to work by replacing the MIN and MAX in the IF statement with SELECTEDVALUE.  I thought when I'd done that initially it didn't work, but it does seem to.  Thanks for your help.

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)