Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
sgaydarska
Frequent Visitor

Line chart to always show latest 52 weeks based on trade this year

Hi,

 

I need to have the below line chart:

sgaydarska_0-1606833001830.png

Purpose of visual: show a trend for the last 52 week up to date of trade this year, trade last year and weekly target (only available for 2020)

 

with the following conditions:

- x-axis will always show 52 weeks back from latest week with trade. E.g. if we're in week 44 2020, the chart should show from week 44 2019 to week 44 2020, when we move to week 45 the chart moves 1 week further week 45 2019 to week 45 2020.

- it should ignore the fact we have target and trade ly values for the whole year and focus on the latest week for which we have trade

- If you select the previous year, in this case 2019, it will simply show you full 2019. 

 

In my date table I have the following fields available:

VariableTypeExample
key_date whole number20010401
datetimedate/time01/04/2001 00:00:00
datedate01 April 2001
day_of_weekwhole number1
day_of_periodwhole number1
day_of_quarterwhole number64
day_of_halfwhole number64
day_of_yearwhole number64
id_weekwhole number200120
week_start_datetimedate/time01/04/2001 00:00:00
week_descriptiontextWeek (10) of 2001-04-1
week_of_periodwhole number1
week_of_quarterwhole number 10
week_of_halfwhole number 10
week_of_yearwhole number 10
id_date_week_startwhole number20010401
id_date_week_endwhole number20010407
id_periodwhole number200103
period_start_datetimedate/time01/04/2001 00:00:00
period_descriptiontext2001-2002/P03
period_of_quarterwhole number3
period_of_halfwhole number3
period_of_yearwhole number3
id_previous_periodwhole number200102
id_date_period_startwhole number 20010401
id_date_period_endwhole number 20010505
period_sequence_numberwhole number4
id_quarterwhole number200101
quarter_start_datetimedate/time28/01/2001 00:00:00
quarter_descriptiontext1st Quarter 2001-2001 (Q1)
quarter_of_halfwhole number1
quarter_of_yearwhole number1
id_date_quarter_startwhole number20010128
id_date_quarter_endwhole number20010505
id_halfwhole number200101
half_start_datetimedate/time28/01/2001 00:00:00
half_descriptiontext1st Half 2001-2001 (SS)
half_of_yearwhole number1
id_date_half_startwhole number20010128
id_date_half_endwhole number20010804
id_yearwhole number2001
year_start_datetimedate/time28/01/2001 00:00:00
year_descriptiontext2001-2002
id_date_year_startwhole number20010128
id_date_year_endwhole number20020202
batch_idwhole number211

 

as you can see the majority are whole numbers because I need to use fiscal dates. E.g. Fiscal year 2020 starts on 20200302.

 

I came accross the following post: https://community.powerbi.com/t5/Desktop/Rolling-52-week-sales-and-preceding-52-week-sales/td-p/7201... but I can't seem to make it work. 

 

Any ideas?

 

Thanks,

Sani

 

5 REPLIES 5
Anonymous
Not applicable

Hello Everyone,
I am facing exactly same situation for the past one month. However I couldn't figure out the solution.
if anybody has got solution to this issue. Please reply me back as soon as you can. It would be a great help.

Thanks & Regards

v-robertq-msft
Community Support
Community Support

Hi, @sgaydarska 

According to your description, I guess you want to get a line chart that displays the latest 52 weeks based on trade this year, I suggest you to use the relative date range slicer, which can get the result easily, you can take a look at my steps and find if it’s useful:

This is part of my test data(date range from 2017 to 2020):

v-robertq-msft_0-1607048414929.png

 

  1. I created a calculated column to get the Year week:

Week number = YEAR([Date])&"-W"&WEEKNUM([Date])

 

  1. I created a Slicer and change it to “Relative date”-> last 1 year:

v-robertq-msft_1-1607048414931.png

 

  1. I created a Line chart and place columns like this:

88888888888888.png

And I can get what you want.

You can download my test pbix file here

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

I don't think this works because I need to use fiscal dates which are not "Date" variables. hence I cannot use relative date. 

 

Thanks,

Sani

amitchandak
Super User
Super User

@sgaydarska , the best is to use a relative date slicer. https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

 

Or have column like this in your date table

Week Type =
var _date =Today() //or //max(Table[Date]) //max date of table
var _eow = _date+ 7-1*WEEKDAY(_date,2)

Switch( True(),
[start week]<=_eow && [end date]>=_eow -7*45,"45 Weeks " ,
[Week Name]
)

 

And filter 45 Weeks

 

Or refer this video :https://www.youtube.com/watch?v=duMSovyosXE

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi,

 

I don't think this works because I need to use fiscal dates which are not "Date" variables. hence I cannot use relative date. The fiscal part is what I'm struggling with. 

 

Thanks,

Sani

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.