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

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. 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Legend thy name!!!!

     

    Thank you so much Anonymous. Absolute legend!