Unable to get Year on Year data

[nagaraj007]

Hello All,

I am unable to get year on year data for some reason. I have added the formula for getting last year data but it shows blank.

Please help me. I have attached the PBIX file for your reference.

 https://www.dropbox.com/s/mw014251xnkg8rl/need%20help%20on%20yoy.pbix?dl=0 

[AntrikshSharma]

Create a year column in Calendar table instead of using it from Sheet1 table and remove the company name from the visual, YOY works on dates.

[nagaraj007]

Hi,

I got the answer as per your instruction, however the requirement is they want to compare any 2 years based on the companies, Please advise

[nagaraj007]

Output they are looking at

[nagaraj007]

Hi, Can someone help me to get the output as per my previous post. Its very critical

[Anonymous]

Hi  @nagaraj007 ,

I think you only need to calculate in one table,see below:

Create 2 measures as below:

last separate year = 
CALCULATE(SUM('Sheet1'[Value]),FILTER(ALL('Sheet1'),'Sheet1'[Year]=MAX('Sheet1'[Year])-1))

last year = SUMX(SUMMARIZE('Sheet1','Sheet1'[Year],'Sheet1'[Company Name],"last year",'Sheet1'[last separate year]),'Sheet1'[last separate year])

And you will see:

Is above what you need?

Here is the .pbix file.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[nagaraj007]

Hi,

If i use the PBIX which u have provided, if i convert the data from table visual to matrix visual, it shows wrong data. 

We need to show the data in the matrix level, as the user will compare the Company against 2 years of the data and also he wants to see the growth percentage also

Please find the below screenshot

[Anonymous]

Hi  @nagaraj007 ,

In the .pbix file you provided,for the same company,there's only the value for one year,no other years,could you pls update your .pbix file for calculation?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[nagaraj007]

Hi, Yes i am saying for one company there is no data for other years. But the PBIX file which you have shared it shows wrong data.

We would like to have analyis on Company basis based on year selection in a matrix visual along with Growth percentage. 

Below is the Output i am looking at. Please help me.

[v-kelly-msft]

Hi  @nagaraj007 ,

Sorry for the late reply!

In your measure ,you are using SAMEPERIODLASTYEAR('Calendar'[Date]),so first create a calculated column in Calendar table:

Year = YEAR('Calendar'[Date])

And put the column in the filed of the table visual instead of using the year column in Sheet1,then remove the company name from the field,and you will see:

Pls check the attachment for the updated .pbix file.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[nagaraj007]

Hi,

I would like to get the output in the matrix visual. Company wise year on year data. In my previous message i have posted the screensnip. Please advise how to get the data in matrix visual along with growth percentage.

[v-kelly-msft]

Hi @nagaraj007 ，

As I have mentioned before,since with the provided data,one company only has the value in one year,so if you put the company in matrix,the measure will return blank values.

But if you wanna simply calculate the percentage,using below dax expression:

percentage = 
var diff=CALCULATE(SUM('Sheet1'[Value]),FILTER(ALL(Sheet1),YEAR('Sheet1'[date])=YEAR(MAX('Sheet1'[date]))))-'Sheet1'[last year]
Return
IF('Sheet1'[last year]=BLANK()||CALCULATE(SUM('Sheet1'[Value]),FILTER(ALL(Sheet1),YEAR('Sheet1'[date])=YEAR(MAX('Sheet1'[date]))))=BLANK(),BLANK(),DIVIDE(diff,'Sheet1'[last year]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[nagaraj007]

Hi Kelly,

I am struggling to get the required output in the live data. If you could help me with my live data that would be of great help.

Only because of this output i have not published this app to the users.

I have sent the personal message to your inbox. Please help me

[v-kelly-msft]

Hi  @nagaraj007 ,

I just saw a message left in my messages box without any live data.Could you pls provide more details?Or just simple sample as available.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[nagaraj007]

Hi Kelly,

As the data is confidential, if you could let me know the convenient time, we can discuss via Teams meeting request. Please advise.

[nagaraj007]

Hello All,

As the data is confidential, if any one could help me via Teams meeting request. I would be really greatfull to you guys.

Please advise.

[nagaraj007]

Hello All,

Could anyone please help me

[nagaraj007]

Hello All, 

Could anyone help me.

[amitchandak]

@nagaraj007 , I did check the pbix. But these are the two ways with date table or year in a separate table

example

This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))


//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[nagaraj007]

Hi, 

My table has only data without dates, but i just added the dates in the sheet1 to link with date table.

If you can help me to get the output in the PBIX file and share it back that would be of great help.

Its very urgent and i am unable to get the result.

Thanking you in advance