Newbie need help with Variance

[Vladracs]

Hello Experts,

I am still struggling with basics concepts in PBI, hopefully you can enlight me a bit.

I have a big table that I divided into a couple of dim tables (customers, variables) and a fact table with dates and Amount for the dates (1 per month, per customer, ver variable). 

I would like to visualize a table, where I can filter on a Customer basis and then filter on a fixed month and for 1 specific variable and get the Amount for the months in the different years.

And add a new column that shows the difference in Amount from one year to the next (having the lowest year as a base). 

You can see a (fict) sample below.

Date	Customer	Variable	Amount
Jan 2022	A	X	1
Feb 2022	A	X	2
...	A	X	3
Dec 2022	A	X	4
Jan 2021	A	Y	10
Feb 2021	A	Y	20
...	A	Y	30
Dec 2021	A	Y	40

Here with Variance Column, after filtering per customer and month and variable:

Filter (Jan, A, X):

Date	Customer	Variable	Amount	Variance
2020	A	X	1
2021	A	X	10	9
2022	A	X	100	99

I know its a big ask. But if you could point me to the right direction Id be very thankful.

Regards,

Vladimir

[VijayP]

@Vladracs 

The video explains what exactly you want ! First go thru and try and you will get the result as you required.

[Vladracs]

Hello VijayP,

Thx for the hint, I was able to get a column that shows the value of previous year, with
Last Year Value = CALCULATE (
Ranking[sum of values],
SAMEPERIODLASTYEAR ( 'Date'[Data] ) 
)

The problem is I probably didnt explain the requirement fully.

I would like to filter the table for 2 year and get the same result...

say I chose 2018 and 2020, or 2019 and 2022 , so its more previous value in the table (dynamicly sliced by the slicer in the page)...

I saw a solution that tried to index the rows and use Index - 1 to do the division...Problem is the same these indexes have to change as I slice the table...or this wont work as well. 😕

any further hints?

[VijayP · Accepted solution]

@Vladracs 

You have to use SAMEPERIODLASTYEAR Function to find yoy comparison the way you want!

https://www.youtube.com/watch?v=L6kbQi7Mvpk&list=PLWQB3PEUJKRneu8fam5CaCMCdrIdT_zDD&index=2

Watch at 7:52 to find the formula usage. I suggest watch 1 and 2 of the same playlist to understand the outcome much better

[Vladracs]

Thx for the link, very good stuff.

But the problem with most of the info I found so far , deals with values that are acummulating over time. These I am deal with are not. these are individual values. There's no total. So I am looking for some meausre that deals with individual values of each year (as per month/customer/variable). I havent a way to translate from the formulas/measures he uses to what I need 😞