Forum Discussion
Newbie need help with Variance
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
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
4 Replies
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
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 😞
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?
