Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have transposed my report from excel to Power BI. I am very happy with it, nevertheless, there are plenty of things I could do very easily in pivot tables, that I do no manage to do in Power BI.
Below is an exemple : sales by clients, product and year with a comparison in % Y-1.
HOW DO YOU SHOW IN A TABLE THE DIFFERENCE IN % IN BETWEEN TO CULUMNS?
(I do not know sql)
Thank you fo your help.
Eliza
2014 | 2015 | 2016 | ||||
Client | Product | Total HT NET | Total HT NET | Evolution Y-1 | Total HT NET | Evolution Y-1 |
ADUKI | GERMOIR | 5 760 € | 1 332 € | -76,88% | 2 034 € | 52,70% |
aHealthylife.nl | GERMOIR | 3 441 € | 3 121 € | -9,30% | ||
AMARAN | GERMOIR | 126 € | #NUL! | |||
AROMA ZEN | GERMOIR | 3 851 € | 7 226 € | 87,62% | 4 635 € | -35,85% |
AU BOURGEON VERT | GERMOIR | 201 € | 330 € | 64,27% | 540 € | 63,52% |
AUCHAN | GERMOIR | 6 694 € | 5 697 € | -14,90% | 3 281 € | -42,40% |
BIEN ET BIO | GERMOIR | 3 967 € | 4 424 € | 11,50% | 2 623 € | -40,71% |
BienManger .com | GERMOIR | 2 611 € | 4 017 € | 53,87% | ||
BIO C' BON | GERMOIR | 8 871 € | 15 044 € | 69,60% | 20 686 € | 37,50% |
BIO CASH | GERMOIR | 3 294 € | 4 363 € | 32,44% | 4 407 € | 1,03% |
BIO VITALITY | GERMOIR | #NUL! | 1 989 € | |||
BIOCOOP | GERMOIR | 58 846 € | 74 763 € | 27,05% | 98 600 € | 31,88% |
BIODIS | GERMOIR | 1 269 € | 78 € | -93,86% | 1 379 € | 1667,76% |
Hi @Elizabeth,
In your resource data, the date is day level? If it is, you’d better use SAMEPERIODLASTYEAR and DATESYTD function. SAMEPERIODLASTYEAR Function Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, it will change with actual date is you use it combine TOTALYTD function.
First, create a calendar date table using the formula:
Calendar = CALENDAR(MIN(Table1[Date]),MAX(Table1[Date]))
Then create the total sum of current year.
YTD = CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1, Table1[Clicent]),DATESYTD('Calendar'[Date]))
Second, you calculate the total sum of last year using SAMEPERIODLASTYEAR Function.
YTD LY = CALCULATE(Table1[YTD],SAMEPERIODLASTYEAR('Calendar'[Date])) EvolutionY-1=DIVIDE((Table1[YTD LY]-Table1[YTD]),Table1[YTD])
Finally, create a table visule, please select the Client, Product,YTD ,YTD LY and EvolutionY-1 as value level, you will expected result.
If this is not what you want, please share your resurce sample date for analysis.
Best Regards,
Aneglia
Create a new measure :
Measure = calculate ( sum( value) ) , PREVIOUSYEAR( Dates)) -- It will give u the previous year value
Try this one , let me know if any help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |