Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Would appreciate your guidance in getting Y-o-Y change when the Year is available as a whole number only, like so 2018, 2019 etc. And no there is no Date column in the base data or a Year and Month column (from which a Date column can then be created). This is the data provided.
As you are alI aware, it is futile to convert that column into a Date column as it converts to {9 -12 July 1905}
If the sales year is region dependant and not calendar year based will it make a difference?
Since I am unable to upload a file, I have attached a table
Sales Year (Whole Number) | Sales Amount | COGS |
2017 | 11,928,556 | 6,889,539 |
2018 | 30,516,892 | 14,395,851 |
2019 | 42,895,110 | 21,022,703 |
2020 | 24,468,717 | 14,096,441 |
Thanks in advance, W
Solved! Go to Solution.
Hi @Will_Ryu ,
Here are the steps you can follow:
1. Create measure.
Last Year =
SUMX(
FILTER(ALL('Table'),
'Table'[Sales Year]=MAX('Table'[Sales Year])-1),[Sales Amount])
LastY-CurrentY% =
DIVIDE(
[Last Year],
SUMX(
FILTER(ALL('Table'),
'Table'[Sales Year]=MAX('Table'[Sales Year])),[Sales Amount])
)
2. Result:
If you need pbix, please click here.
Y-o-Y change when year is available as a whole number ONLY.pbix
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Will_Ryu ,
Here are the steps you can follow:
1. Create measure.
Last Year =
SUMX(
FILTER(ALL('Table'),
'Table'[Sales Year]=MAX('Table'[Sales Year])-1),[Sales Amount])
LastY-CurrentY% =
DIVIDE(
[Last Year],
SUMX(
FILTER(ALL('Table'),
'Table'[Sales Year]=MAX('Table'[Sales Year])),[Sales Amount])
)
2. Result:
If you need pbix, please click here.
Y-o-Y change when year is available as a whole number ONLY.pbix
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi
I understand you want to calulate Last year sales to define Y-o-Y with the column of Sales year. Please try this
Total_sale = SUM(Amount)
Last_year_sale = CALCULATE (Total_sale,'[Sales Year] -1)
Y-o-Y = (Total_sale-Last_year_sale)/Last_year_sale
Thank you Hugo
However, using your method, I got the following result
Sales Year | Sales Amount | LY | Y-o-Y |
2017 | 11928556 | 109809275 | -89.14% |
2018 | 30516892 | 109809275 | -72.21% |
2019 | 42895110 | 109809275 | -60.94% |
2020 | 24468717 | 109809275 | -77.72% |
Hi
Please try modify the measure Last_year_sale
Last_year_sale =
var year = MAX (Sales year)
return CALCULATE (Total_sale,year -1)
Hi,
If I am not mistaken you are looking for the Year to Year changes. For this purpose :
1- In Power Query I created the Index column in the particular table :
2- Then in Power BI, created the below Column :
Appreciate your Kudos
@MahyarTF Your solution works great on the simple table with one entry for each year but I wasn't able to extend it to this data set.
Sales Year | Country | Sales Amount |
2018 | Australia | 2,699,691 |
2018 | Canada | 3,704,560 |
2018 | France | 456,704 |
2018 | Germany | 552,620 |
2018 | United Kingdom | 593,106 |
2018 | United States | 15,854,209 |
2019 | Australia | 2,221,468 |
2019 | Canada | 5,908,467 |
2019 | France | 2,119,601 |
2019 | Germany | 663,592 |
2019 | United Kingdom | 2,191,569 |
2019 | United States | 20,965,411 |
2020 | Australia | 5,734,177 |
2020 | Canada | 6,742,743 |
2020 | France | 4,675,251 |
2020 | Germany | 3,662,088 |
2020 | United Kingdom | 4,886,046 |
2020 | United States | 26,177,970 |
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |