Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

X-axis for Line chart

Hi PBI experts

 

I was hoping if I can get some help here.

 

The requirement is to replicate the below Excel into PBI report.

 

Datasets, measures all sorted. I need help with X axis.

 

The way it is set up in excel - only DD-MMM is on the x-axis and the lines indicate current year, previous year, previous to previous year. The measures for these years are all set. Just not getting the X-Axis.

 

 

 

If I add a DD-MMM column, then it takes it as Char and shows continous like this -

 

 

Concern above is that it will need scrolling. 😞

 

If I use a proper date - then it will show the year of the date, which will be most likely current year. This will "confuse" the users.

 

Any help is much appreciated.

 

Thank you

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    The code is to build a calendar table with all column we need directly. If you have a calendar table, you can add some calculated columns. Column name Rank/NewRank/.../Groups are column names in each calculated column code. Copy each column code and replace _T/_T1/.../T4 as your calendar table name. Then you can get result.

    Calendar table:

    Calculated columns :

     

    Rank = RANKX(FILTER('Table',[Year] = EARLIER([Year])),[Date],,ASC) 
    NewRank = IF(MOD([Year],4) = 0,[RANK],IF([Month]<=2,[RANK],[RANK]+1))
    A = IF(MOD([NewRank],4) = 0,INT([NewRank]/4)-1,INT([NewRank]/4))
    Group = MINX(FILTER('Table',[A] = EARLIER([A])),[DD-MMM])

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

     

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , the continuous axis is only supported Date and number.

    You can play with max category width, MAx size, padding, etc to make sure it is no scrolling.

     

    As of now, there is no way to make char as continuous ( there is an option in the new version, but reverts for text/char)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks amitchandak 

      We tried all those options. Still the same result. We cannot fit 90 points in 1 graph. 360/4=90 

      Combining 4 days in 1. That's how Excel has it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Bump. I really appreciate any help here.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous 

      If you want to combine four days as one in your X-axis, try to add a date flag.

      Here I build a calendar table, and relate this table with data table by date column.

      Calendar Table = 
VAR _T = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"DD-MMM",FORMAT([Date],"DD-MMM"))
VAR _T2 = ADDCOLUMNS(_T,"RANK",RANKX(FILTER(_T,[Year] = EARLIER([Year])),[Date],,ASC))
VAR _T3 = ADDCOLUMNS(_T2,"NewRank",IF(MOD([Year],4) = 0,[RANK],IF([Month]<=2,[RANK],[RANK]+1)))
VAR _T4 = ADDCOLUMNS(_T3,"A",IF(MOD([NewRank],4) = 0,INT([NewRank]/4)-1,INT([NewRank]/4)))
VAR _T5 = ADDCOLUMNS(_T4,"GROUP",MINX(FILTER(_T4,[A] = EARLIER([A])),[DD-MMM]))
RETURN
_T5

      Here is the calendar table, we can see in it four days are a group. Sort Group column by A, and use it as X axis in line chart.

      Result:

      Best Regards,

      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        This is fantastic Anonymous . I think I'm almsot there.

         

        Can I trouble you and ask - what if I already have a Calendar table then how do I convert the workings here to a calculated column?

        Or if yo can explain your workings in plain English I will give it a shot.

         

        I can see that you are ranking the dates, also considering for the Leap year. And then lookup the min date that falls for the Mod calculated.

         

        I've not worked with Add_Columns - if I use the formula after naming of the column, that should help me with calculated column, right?

         

        Thank you so much.