Forum Discussion

KMcCarthy9's avatar
KMcCarthy9
Helper V
5 years ago
Solved

Week Number Showing Twice

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"
    )

 

  • 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

     

3 Replies

  • 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

     

  • 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.