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
Anonymous
Not applicable

Week appears twice in the area chart because of the date hierarchy

I am using calculated Calendar table (below is the code) and the following hierarchy using the columsn from Calendar table:
Date Hierarcy
-Year
-Month
-WeekNum
-Day

I have an area chart, where on the Y axis I have number of orders and on the X axis Date Hierarcy (that I specified above). When I drill down to the WeekNum, then the week 49th of year 2020 appears twice it belongs to two months November and December. 

I understand that this phenomena happens because I have "Month" in the "Date Hierarchy".
However, I am wondering how I can make the week 49th appear only once in the chart? And still be bale to drill up to the month.



Calendar = (ADDCOLUMNS(
CALENDAR("01.01.2018","31.12.2022"),
"Year", YEAR([Date]),
"NumMonth", MONTH([Date]),
"Month", upper(FORMAT([Date],"MMM")),
"WeekDay", WEEKDAY([Date],2),
"WeekNum", WEEKNUM([Date],2),
"Day", DAY([Date]),
"Quarter", FORMAT([Date],"Q"),
"Year&Month", upper(FORMAT([Date],"MMM")) & " " & YEAR([Date])))



Problem:

6 REPLIES 6
Greg_Deckler
Super User
Super User

@Anonymous OK, a little confused that you say nope and then state what I stated but I'll get over it. 🙂 Probably would look something like this:

Custom Weeknum Column:
  VAR __Weeknum = WEEKNUM([Date])
  VAR __NextMonth = EOMONTH([Date])+1
  VAR __WeeknumNextMonth = WEEKNUM(__NextMonth)
RETURN
  IF(__Weeknum = __WeeknumNextMonth,__Weeknum-1,__Weeknum)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler nope. 30th of November should be under week 48. My ultimate goal is to have month NOT being displayed when drilled down to WeekNum. Just imagine, if I would have simply chosen X axis to be Weeknum, then I would still have only one week 48. But because the the labels year-month are also displayed, I happen to have two 49 weeks.

The goal is to have labels being displayed wonly for the current drill (year or month or week num or day) but not all together (Year/month/week num) when drilled to week num.

daxer-almighty
Solution Sage
Solution Sage

The simple answer is: YOU CAN'T. At least not with the standard calendar. You have to use a different one - like, say, the 445 calendar - to do what you want. Please go to www.sqlbi.com and search for articles on working with calendars.

Anonymous
Not applicable

Hi, @Greg_Deckler thank you for replying so fast! I'm not sure why would a model and some other calculations be relevant for this problem.

I attach a screenshot of the problem. The calendar week is calculated as I specified above in the main post.  You can find how the date hierarchy is built on the right of the screenshoot.

The problem is that I want to be abe to drill from year to month to the week to the day. And of course, a week can belong to two months, meaning because of the X-axis drilling, such a week will appear twice.

In the second screenshot you see exactly why the week 49 appears twice - it is because the 30th of November (Monday) belongs to November and week 49. But week 49 also has days from December.
 I am pretty sure I am not the first one who has this problem, so I guess there should be an easy solution.



Screenshot 2020-12-16 at 18.45.40.png

Screenshot 2020-12-16 at 20.10.38.png

@Anonymous So, would you want the 30th of November to appear as week 48? 

 

Also, I did a lot of crazy calendar manipulation stuff in this post: DAX Custom 445 Calendar Greg_Deckler  1 (powerbi.com)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@Anonymous - Very difficult as I think if you performed a check whether a weeknum occurs in 2 different months of the year and then basically return blank for the weeknum in either the previous or subsequent month that is likely to negatively impact calculations I would have to believe. Difficult to say without knowing more about your model and calculations.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.