Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Plotting data on week numbers in a graph over years

Hi guys,

I'm working with a fact table and a date table to build my dashboard. My fact table has data by the week over several years. It contains a column with week numbers (actually it's year-week, so 201901, 201902, etc)

 

Is there any way to prevent Power BI filling in the "blanks" when making a graph based on week numbers?

 

Right now my slicer goes up to 201999, while there is only data up until 201952 and then continues with 202001.

 

Thanks in advance for your help!

 

EDIT: When I change the x-axis to categorical, it doesn't really solve my problem as I get a huge scrollbar.

Need Help

7 Replies

  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    Anonymous 

     

    Try with Categorical for x axis. 

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      VasTgThank you for your reply. Please see my edit.

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    We can try to create a calculated column using following Dax as the x-axis to meet your requirment:

     

    DateColumn =
DATE ( ROUNDDOWN ( [WeekNr] / 100, 0 ), 1, 1 ) + [WeekNr]
    - ROUNDDOWN ( [WeekNr] / 100, 0 ) - 1


    Best regards,

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-lid-msftThank you for your insight. What would the result of that calculated column be? I'm getting a sequence of dates in the year 2563, so 19-9-2563, 20-9-2563, 21-9-2563, etc.

       

      Using the formula:

      DATE ( ROUNDDOWN ( Table[WeekNr] / 100 ; 0 ); 1; 1) + Table[WeekNr] - ROUNDDOWN ( Table[WeekNr] / 100; 0 ) - 1
      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        Sorry for our incorrect DAX formula, we want to get the date of each weeknr, but after read your requirement again, it seems like you want to keep show week number in the axis, we are trying to find the solution and will update here if we find it.

         

        D = DATE ( ROUNDDOWN ( 'Table'[WeekNr] / 100 , 0 ),1, 1) + 7*('Table'[WeekNr] - ROUNDDOWN ( 'Table'[WeekNr] / 100, 0 )*100 - 1)

         

         


        Best regards,