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

     

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.