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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DomantasK
Frequent Visitor

Week error because of year change

Hello,
Could anyone consult me what would be the right way to display the chart in this situation:
I'm trying to display data weekly and because of that, there is some missunderstandings in year change between 2021 and 2022.
Function "WEEKNUM" and inserted column in power query shows wrong week number and to make it right, I need to substract every week number by one (1-). Because of that, "Week 0" in year 2022 appears. By the fact, it should be merged with means from 52 week of 2021 year.
Maybe there is any way to combine week 2021 and week 2022 by using DAX functions? Or something with queries should be done? I've tried to replace the 2022_0 week means with 2021_52 week in queries, however, then 2022_52 week appears in the chart because "Data" date hierarchy exists and gap between years is still wrong.

DomantasK_0-1666185735181.png

DomantasK_1-1666185769769.png
It should be like this: 

DomantasK_2-1666186647193.png

 

Is there any way possible to keep the date hierarchy? Because if I'm adding new mean with "Years" means only, then flexibility with date hierarchy is gone.
Thank you

 

1 ACCEPTED SOLUTION

Hi @DomantasK ,

 

Modify the [Year] column like below:

Year = IF('Table'[Week] = 52, 2021, YEAR('Table'[Date]))

vyadongfmsft_0-1666317408601.png

 

The chart is normal:

vyadongfmsft_1-1666317469531.png

 

Best regards,

Yadong Fang

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

4 REPLIES 4
DomantasK
Frequent Visitor

Thank you! 🙂 

v-yadongf-msft
Community Support
Community Support

Hi @DomantasK ,

 

This is my test table:

vyadongfmsft_0-1666248423457.png

 

Create a week column like this:

 

Week = IF(WEEKNUM('Table'[Date],2)-1 = 0 && YEAR('Table'[Date]) = 2022, 52, WEEKNUM('Table'[Date],2)-1)

 

vyadongfmsft_1-1666248588951.png

 

Week 0 will not appear in 2022:

vyadongfmsft_2-1666248712195.png

 

Best regards,

Yadong Fang

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

Hey, @v-yadongf-msft ,thanks for answering! 
However, now mean in year 2022 week 52 appears, while, I need it to combine this value with values in 2021_52. 
This is your graph:
Capture.PNG

 

My graph currently looks like this:
DomantasK_0-1666260944361.png
Is there any way that formula could be updated to fix this?

Thank you in advance!

 

Hi @DomantasK ,

 

Modify the [Year] column like below:

Year = IF('Table'[Week] = 52, 2021, YEAR('Table'[Date]))

vyadongfmsft_0-1666317408601.png

 

The chart is normal:

vyadongfmsft_1-1666317469531.png

 

Best regards,

Yadong Fang

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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