Bar chart with last 6 weeks taking into account switching year

[JuanBolas]

Hello,

I'm need to display a bar chart with the number of the number of trips (simply counting Carrier field) for each week over the last 6 weeks of data from the date of the last date entered in the date field (max of date).

I've tried a couple of things but it doesn't work. A relative date filter requires that I put in last 10 weeks to shows the las 6 weeks for some reason and it doesn't take the date of the last data entered into account.

It especially messes up if the last date is one of the first weeks of January.

My test data is here: 

https://drive.google.com/file/d/1SbBRhnigYuCoHPik18k1MLOk1vWe-OLn/view?usp=sharing

Thanks in advance.

[v-easonf-msft · Accepted solution]

Hi, @JuanBolas 

Make sure you have a calendar table with consecutive dates like below:

Calendar = CALENDAR(Min(Table1[Date Numbering]),MAX(Table1[Date Numbering]))

Add new calculated column 'Yearweek'(whole number) ,'Rank' (whole number)

Yearweek = YEAR('Calendar'[Date])&WEEKNUM('Calendar'[Date])

Rank = RANKX('Calendar','Calendar'[Yearweek],,DESC,Dense)

Then you can use new field 'Rank' to filter the chart.

result:

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