Add Last Three Years and %YoY in Matrix Table

[sukumararumugam]

Hi All,

I'm very new to Power BI, can any one let me know how to derive the below result thru Matrix table?

[sukumararumugam]

Please note that the years are should be dynamic and not constent. Meaning it should change based on the selection

[v-shex-msft]

Hi @sukumararumugam,

If you can please provide some sample data with expected result to help us understanding your requirement and coding formula.

Regards,

Xiaoxin Sheng

[sukumararumugam]

[Ashish_Mathur]

Hi,

In your base data, do you have a Month column?  Or still better, do you have a Date column?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[sukumararumugam]

Hi Ashish..

You can access the sample from the below link.. Let me if you can't.

https://1drv.ms/u/s!AvuiYIpqHEiqgQPpZshW3vvvLs82

Thank you so much for your support!

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

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

Hope this helps.

[sukumararumugam]

Hi Ashish,

Thanks you so much for helping me on this!

Can you Please help me in understaning the below function - 

DATESBETWEEN('Calendar'[Date],EDATE(MAX(Sheet1[Date]),-3)+1,MAX(Sheet1[Date]))

in our case I know the MAX(Sheet1[Date]) will return 4/1/2018 and if I do -3 then it will return 1/1/2018  by adding 1 then 1/2/2018..

as per the above understaning the result will be wrong. However the results on your file is right. So for sure I'm something here can you please help me in understanding.

Thank you once again!

Sukumar.

[Ashish_Mathur]

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]

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]

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]

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

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

Thanks!

[Ashish_Mathur]

I don't think so.

[sukumararumugam]

Hi Ashish,

Sorry for the late reply.. Yes I have Column for each date and also seperate field for Month.

Thanks!

[sukumararumugam]

Also is it possible to get the below view in Power BI, where Q2'QoQ will be dynamic- meaning based on the latest Quarter selected Vs Previous Quarter.