March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I need to have the below line chart:
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:
Variable | Type | Example |
key_date | whole number | 20010401 |
datetime | date/time | 01/04/2001 00:00:00 |
date | date | 01 April 2001 |
day_of_week | whole number | 1 |
day_of_period | whole number | 1 |
day_of_quarter | whole number | 64 |
day_of_half | whole number | 64 |
day_of_year | whole number | 64 |
id_week | whole number | 200120 |
week_start_datetime | date/time | 01/04/2001 00:00:00 |
week_description | text | Week (10) of 2001-04-1 |
week_of_period | whole number | 1 |
week_of_quarter | whole number | 10 |
week_of_half | whole number | 10 |
week_of_year | whole number | 10 |
id_date_week_start | whole number | 20010401 |
id_date_week_end | whole number | 20010407 |
id_period | whole number | 200103 |
period_start_datetime | date/time | 01/04/2001 00:00:00 |
period_description | text | 2001-2002/P03 |
period_of_quarter | whole number | 3 |
period_of_half | whole number | 3 |
period_of_year | whole number | 3 |
id_previous_period | whole number | 200102 |
id_date_period_start | whole number | 20010401 |
id_date_period_end | whole number | 20010505 |
period_sequence_number | whole number | 4 |
id_quarter | whole number | 200101 |
quarter_start_datetime | date/time | 28/01/2001 00:00:00 |
quarter_description | text | 1st Quarter 2001-2001 (Q1) |
quarter_of_half | whole number | 1 |
quarter_of_year | whole number | 1 |
id_date_quarter_start | whole number | 20010128 |
id_date_quarter_end | whole number | 20010505 |
id_half | whole number | 200101 |
half_start_datetime | date/time | 28/01/2001 00:00:00 |
half_description | text | 1st Half 2001-2001 (SS) |
half_of_year | whole number | 1 |
id_date_half_start | whole number | 20010128 |
id_date_half_end | whole number | 20010804 |
id_year | whole number | 2001 |
year_start_datetime | date/time | 28/01/2001 00:00:00 |
year_description | text | 2001-2002 |
id_date_year_start | whole number | 20010128 |
id_date_year_end | whole number | 20020202 |
batch_id | whole number | 211 |
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
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
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):
Week number = YEAR([Date])&"-W"&WEEKNUM([Date])
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
@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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
98 | |
85 | |
69 | |
61 |
User | Count |
---|---|
138 | |
120 | |
109 | |
99 | |
97 |