Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
It should be like this:
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
Solved! Go to Solution.
Hi @DomantasK ,
Modify the [Year] column like below:
Year = IF('Table'[Week] = 52, 2021, YEAR('Table'[Date]))
The chart is normal:
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.
Thank you! 🙂
Hi @DomantasK ,
This is my test table:
Create a week column like this:
Week = IF(WEEKNUM('Table'[Date],2)-1 = 0 && YEAR('Table'[Date]) = 2022, 52, WEEKNUM('Table'[Date],2)-1)
Week 0 will not appear in 2022:
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:
My graph currently looks like this:
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]))
The chart is normal:
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |