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

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

Legend thy name!!!!

 

Thank you so much Anonymous. Absolute legend!