Forum Discussion

sukumararumugam's avatar
sukumararumugam
Frequent Visitor
8 years ago
Solved

Add Last Three Years and %YoY in Matrix Table

Hi All,   I'm very new to Power BI, can any one let me know how to derive the below result thru Matrix table?
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    I believe this is the result you want.  You may download my PBI file from here.

     

    Hope this helps.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

You are welcome.  The MAX(Sheet1[Date]) will return the last date of the last quarter.  So, if you have sales data till Q2 of 2018, the MAX(Sheet1[Date]) will return June 30, 2018.  The EDATE() function goes back 3 months.  So the result would be March 31, 2018.  1 added to this will reutrn April 1, 2018.  So the DATESBETWEEN() function will reurn a Table of dates from April 1, 2018 to June 30, 2018.

 

Hope this clarifies.

sukumararumugam's avatar
sukumararumugam
Frequent Visitor
8 years ago

But how come MAX(Sheet1[Date]) will return 6/30?? the maximum date in that filed is 4/1/2018

 

 

 

Sorry not able to catch the function :(

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    The quarters that you see in the column area section of the matrix visualisation are from the Calendar Table (not from sheet1).  The last date of Q2 as per the Calendar Table is June 30, 2018

  • sukumararumugam's avatar
    sukumararumugam
    Frequent Visitor
    8 years ago

    Got it!! Thank you so much. Much appreciated!

     

    One last question , is there a way to rename the "Total" to "QoQ %" ???

    Thanks!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    I don't think so.