Display original values and their difference in the same table

[Anonymous]

Hi,

I would like to have something like this displayed in a table in PowerBI. I have the Year values (the original ones) and I want to make the difference between them.

The catch is that every month I need to make a new difference (like using a filter), but have it all displayed in a table. Is this even possible? 

Company	Year	Value
A	2019 jan	20
A	2018 jan	5
A	difference	15
B	2019 jan	16
B	2018 jan	6
B	difference	10

[v-yiruan-msft]

Hi @Anonymous ,

You can refer the contents in the following links to achieve it:

Using SAMEPERIODLASTYEAR To Compare The Difference Between This Year & Last Year

Calculating the difference between a specific month, and the following months

Best Regards

Rena

[Anonymous]

Thanks to you both. I've managed to get the difference between the months I wanted (selected by a filter), but I can't display that in the format I need, since the original values are in a column and the difference in a measure format.   Is it possible to achieve that? I mean like this the table I posted above?

[v-yiruan-msft]

Hi @Anonymous ,

What's your expected result? Could you please just make examples to explain? Then we can provide you a proper solution. Thank you.

Best Regards

Rena

[Anonymous]

I'm sorry in advance if I dont use the correct terms, since I'm kinda new to PBI.

So, simplifying, I have this data with the following columns: Company names, Date, Number of acidents.

And what I want is 2 things:

1. Calculate the difference between 2 months (like jan 2018 and jan 2019). This I could achieve using a measure.

2. I want to display it in a table format like (its my company way of display, so I cant change that):

Company name	Date	Nº acidents
A	jan 2020	20
A	jan 2019	15
A	difference between 2020 and 2019	5
B	jan 2020	5
B	jan 2019	2
B	difference between 2020 and 2019	3

Each month I need to filter the information. So, since we are in july, I need do show the july results. Next month I need to show the august results and so on..

Is this even achievable? Since I have the "original data" in a column format and the difference between months in a measure format.

Thanks for your help!

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I just created a sample pbix file, please check check whether that is what you want.

Table 2 = UNION(VALUES('Table'[Date]),ROW("Date","difference between 2020 and 2019"))

Measure = 
var _curYear= CALCULATE(SUM('Table'[Number of accidents]),FILTER('Table','Table'[Company name]=MAX('Table'[Company name])&&'Table'[Date]=MAX('Table'[Date])))
var _preYear=CALCULATE(SUM('Table'[Number of accidents]),FILTER('Table','Table'[Company name]=MAX('Table'[Company name])&&'Table'[Date]=MIN('Table'[Date])))
return switch(SELECTEDVALUE('Table 2'[Date]),"difference between 2020 and 2019",_curYear-_preYear, CALCULATE(SUM('Table'[Number of accidents]),FILTER('Table','Table'[Company name]=MAX('Table'[Company name]))))

Best Regards

Rena

[Anonymous]

Sorry for the late response, I was on holidays.

Back to the point, thanks a lot for your help! I think it is close but still not the final solution.

As you can se, the difference in company B is not 5. In your formula switch formula at some point you put a "5" where I think there needs to be the difference between the years. Right?

Still, I was not able to do it.. Any thoughts?

[amitchandak]

@Anonymous , assume you have only two months, you have to create a new table and join it with the first table

summarize( Table,table[Company], "Year",0, "Month ", [This Month] -[lastMonth])

DAx Join https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Month wise diff

https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e