cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Elizabeth New Member
New Member

Year on Year data comparison

 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

 

  20142015 2016 
ClientProductTotal HT NETTotal HT NETEvolution Y-1
Total HT NETEvolution Y-1
ADUKI GERMOIR5 760 €1 332 €-76,88%2 034 €52,70%
aHealthylife.nlGERMOIR 3 441 € 3 121 €-9,30%
AMARANGERMOIR 126 €  #NUL!
AROMA ZENGERMOIR3 851 €7 226 €87,62%4 635 €-35,85%
AU BOURGEON VERTGERMOIR201 €330 €64,27%540 €63,52%
AUCHANGERMOIR6 694 €5 697 €-14,90%3 281 €-42,40%
BIEN ET BIOGERMOIR3 967 €4 424 €11,50%2 623 €-40,71%
BienManger .comGERMOIR 2 611 € 4 017 €53,87%
BIO C' BONGERMOIR8 871 €15 044 €69,60%20 686 €37,50%
BIO CASHGERMOIR3 294 €4 363 €32,44%4 407 €1,03%
BIO VITALITYGERMOIR  #NUL!1 989 € 
BIOCOOPGERMOIR58 846 €74 763 €27,05%98 600 €31,88%
BIODISGERMOIR1 269 €78 €-93,86%1 379 €1667,76%
2 REPLIES 2
Baskar Super Contributor
Super Contributor

Re: Year on Year data comparison

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 

v-huizhn-msft Super Contributor
Super Contributor

Re: Year on Year data comparison

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 935 guests
Please welcome our newest community members: