 Hey guys welcome to SSUniTek Sushil this side and this is continuation of Power BI scenarios. So today we are going to discuss one more real time scenario and where we will see about the DAX formula. So how we can achieve the output by using the DAX formula. So basically here as you can see we are having input table that would have dealer then the year and then the value. So we are having these three columns. And in the output you can see we are expecting to first column that should be the row which will be containing the years. After that you can see the dealers in the columns like Arihan, CBS, Genpak, SSU and the total value of that. And here you can see the sales then the last year sales then the percentage growth. So here we are having the catch like we need to create these three majors those will be going to calculate. First is the sales of that particular selected year like 2017, 2018, 2019, 2020. And in the last year sales for example if we are having in the second row that is 2018 so it should have the sales for the 2017 as you can see it is 5400. After that you can see going forward we are having the value for previous years. Then the percentage growth as you can see in 2018 the sale is 510 and previous year sale is 5400. So it is very high so you can see it is going to decreased by 90% approximate. So if we are having the negative value then the background color that should be red and if we are having the positive value then we should have the green background color. So how we can achieve this output? So first of all we need to notice we need to create three majors for the sales last year sales and percentage growth. So go to on the trending one and try to create this. So before going to create those majors go to on the data and quick look of the data. So here you can see first is the year then the values then the dealers. So we are having only these three required columns. Let me add a first measure that will be going to sum of the value which is the total sale. So let me call this measure as sales and it should be sum of sales value. Let me close the bracket and put enter. So now we have created first measure go to on the report view and let me add a matrix. So here this is the matrix and in the rows we want to see the year. So we can drag and drop the year on the rows and in the columns we want to see the dealer. So we can drag and drop that. Here you can notice it is having the hierarchy but we want to see the exact year. So we can select that. Now we have configured the rows and columns in the values we have created the first one that is the sale. We can drag and drop that over here. So here we are having all these sales but second column inside these dealers the last year sale. So how we can calculate the last year sale. So let me add a new measure. So we can click on the new measure and let me call this as last year sales. Here we need to use the calculate function after that you can see the expression. So in the expression we want to see the what we have calculated in the measure that is sales. So we can directly use that measure then put comma here we need to use the date add function. So here we are having the date add after that it is asking for the date. So we are having year column as date then put comma here it is asking the interval. So that should be minus one because we are calculating for the previous year. Then we want to subtract year or we can close the bracket and we can put the enter. So it should be the last year sale. Now let me try to drag and drop this last year sale in the values and we will see. So here it is working as we are expecting for the last year. The last thing that we need to take care for the percentage growth. So the third measure that should be percentage growth. Let me click on this and add a new measure. Let me call this as percentage growth. So it should be percentage growth. So here what will be the percentage growth of this. So that will be sales minus last year sale after that we can close the bracket and here we can close the bracket and it should be multiplied with the 100 and should be divide with the last year sale after that put enter. So it should be the percentage growth. So let me try to drag and drop this percentage growth in the values as well. So here you can see it is going to define values like this. But here you can notice we are having the infinity value because here this value is blank. So what we need to take care about on the growth again. So here we need to check about this last year sales. If the value of this last year sale is equals to zero then we want to see zero. Otherwise we need to do this calculation. Let me put enter. So here it should be zero and other values are as we are expecting. Now go to on the format of this and here go to on the style of this and let me try to make it bold header like this. After that here you can see the conditional formatting. So inside the conditional formatting go to on the background color of this go to on the advanced controls and inside the advanced control we can make the change on these. So here first of all let me cancel this let me off this here we need to select the column so our column is percentage growth. Now click on background color and go to on the advanced control. So here we can see we are having the rules so go to on the rules in the format by and here if this value is greater than about this and instead of a percentage it should be number and less than or equals to zero then we want to display this as a red. Add a new rule and this time it should be greater than or equals to one instead of percentage it should be number and less than we can define a last number. That should be green so go to on the custom color and here let me use like this. So it is the green color now we can click on. So here it is going to change according to our requirement but here the font color that should be white of these. So again go to on the font color and this time go to on the advanced and here the lowest value that should be white and the highest value that will be again white click on. So this looks good but here in the first row as you can see the year so it is having the Sunday then the date and year both but we want to see the year only. So how we can make this change go to on the data view select that column go to on the format and we want to see the year only. So we can select the year and it is going to change now go to on the report view and here you can see the year only like 2017, 18, 19 and 20. One more thing like go to on the data so let me roll back this. Here it is going to calculate the data for the last one year. For example today is the first so it is going to have the data for previous year first date like first of the Jan 2016 for the first row. So if you could have any scenario like this so I would request to you please create one more column on the date and that will be always hard code first of Jan and year you need to pick from this column. So all those calculations will be done on that newly created column and you can make the changes as we did in this video. And if you have any doubt then you can reach out to us and you can drop your questions on the comment box so I will try to response on your questions. Thank you so much. See you in the next video with new scenario.