cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
afk Regular Visitor
Regular Visitor

Dynamic date charts (for week number)

Hi,

 

How do I create a chart that only shows data for the past 6 weeks for example? I have a weeknum column and I've put this column on the x-axis of the charts.

 

At the moment, the chart shows all week numbers that exist in the data and will continue to build when more data is inputted. So over time this will get cramped hence why i only want to show the past 6 weeks.

 

Thanks!

5 REPLIES 5
jthomson New Contributor
New Contributor

Re: Dynamic date charts (for week number)

If your week number is being derived off some sort of date field, what you can do is put the date field in the visual filter section and use the relative date filtering to say you're only wanting data within the last 42 days. Not sure how it would work if it isn't, perhaps make some sort of calculated column that has a week number running continuously (so that it doesn't reset year on year), then look up the largest value (to get the current week number), and do some DAX to take that off of your new column, add six, then filter on positive values?

afk Regular Visitor
Regular Visitor

Re: Dynamic date charts (for week number)

Hi @jthomson,

 

Thanks for your suggestion. Yes, my week number is derived from a date field so this suggestion works easily. However, this only works well if the data is constantly up to date.

 

Currently, my data is only dated up to mid Jan. Is there a way for the charts to show the latest 6 weeks from the latest date in the data? 'MAX(date) - 6 weeks' for example.

Community Support Team
Community Support Team

Re: Dynamic date charts (for week number)

HI @afk,

 

In my opinion, I'd like to suggest your take a look at relative date slicer. I think it may suitable for your requirement.

Use a relative date slicer and filter in Power BI Desktop

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
Highlighted
afk Regular Visitor
Regular Visitor

Re: Dynamic date charts (for week number)

Hi @v-shex-msft,

 

This is similar to the previous suggestion, but the same issue occurs. It takes the latest [#] weeks from today's date. But if the data is not up-to-date, then it doesn't work so well. In my case, the latest data date is 16/01/2018, and if I choose to show the last 6 weeks, it only shows me 3 weeks. If i choose to show the last 1 week, it appears as blank as i don't have any data for the past week.

 

Any suggestions for this?

Community Support Team
Community Support Team

Re: Dynamic date charts (for week number)

HI @afk,

 

Did you use week(calendar) option on relative slicer? If this is a case, week(calendar) only shows finished weeks.

 

I'd like to suggest you use week option to instead.(it calculate on day number)

8.PNG

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 58 members 1,208 guests
Please welcome our newest community members: