Week Number Showing Twice

[KMcCarthy9]

Hello, I have a stacked column graph that I want the X axis to show the values by month/year and then drill down into week number. The visual I created does this but at times shows the same week number twice - when the 1st day of the month is in the middle of the week.  In my sample this happens at the end of April 2020, and beginning of May 2020, as well as a few other places. 

I created DAX that fixes the week number issue, however when I use it I am unable to show the values by month/year...it only shows them by month with values from both years aggrated into one month column for a specific month (ie, Feb 2019 and Feb 2020, are only showing as Feb). 

I want to be able to see values by year/month and then drill down to the week numbers but only have a week number showing once. 

I have included a sample .pbix file here: .pbix file 

Thank you! 

DAX that fixes week issue


eeknostr = 
FORMAT (
    YEAR ( Datetable[Date] - ( WEEKDAY ( Datetable[Date], 2 ) - 2 ) ),
    "0000")

& "-"
     & SWITCH (
        MONTH ( Datetable[Date] - ( WEEKDAY ( Datetable[Date], 2 )-2 ) ),
        1, "Jan-",
        2, "Feb-",
        3, "Mar-",
        4, "Apr-",
        5, "May-",
        6, "Jun-",
        7, "Jul-",
        8, "Aug-",
        9, "Sep-",
        10, "Oct-",
        11, "Nov-",
        12, "Dec-"
    )
    & FORMAT (
        WEEKNUM ( Datetable[Date] - ( WEEKDAY ( Datetable[Date], 2 ) - 2 ),1 ),
        "00"
    )

[KMcCarthy9 · Accepted solution]

I was able to solve this by creating this measure and just using the date hierarchy of it, instead of separately bringing in a month and week column. 

Week Start Date = DateTable[Date] - WEEKDAY(DateTable[Date],2) + 1

[amitchandak]

@KMcCarthy9 , No access to file. I think this is because when expand show both month and week. Week split across month and it is shown twice.

[KMcCarthy9]

@amitchandak Sorry about that. Try this file: .pbix file.

Is there a way to stop it from showing both the month and week number on drill down?

Thanks!