Forum Discussion
Calculating the difference between a specific month, and the following months
Hi guys.
This is my first message in the power bi community.
I'm counting on your help
I need to calculate for each year, the difference between the stock value of Decembre( the last month of the last year), and stock value of all the following months (of the current year).
here is the link to the Excel file that i'm using.
https://drive.google.com/open?id=1CtnvMq3mpexlESyyVLjbhQZo6H12RmR2
Hi@ Ghaston
After my research by your new file,You can use this measure
Column 2 = CALCULATE( SUM( Data[Val.stk va]), FILTER( Data, Data[Groupe marchandises]=EARLIER(Data[Groupe marchandises])&&Data[Year]=EARLIER(Data[Year])&&Data[Month]=EARLIER(Data[Month])))- CALCULATE( SUM( Data[Val.stk va]), FILTER( Data, Data[Groupe marchandises]=EARLIER(Data[Groupe marchandises])&&Data[Year]+1=EARLIER(Data[Year])&&Data[Month]=12))Result:
Here is demo, please try it
Best Regards,
Lin
5 Replies
Hi@ Ghaston
After my research, you can do these follow my steps like below:
Step1:
Add two columns
Year = YEAR(Data[Mois]) Month = MONTH(Data[Mois])
Step2:
Add the calculate column
Column = IF ( Data[Month] = 12, CALCULATE ( SUM ( Data[Stock] ), FILTER ( Data, Data[Type d'article] = EARLIER ( Data[Type d'article] ) && Data[Year] = EARLIER ( Data[Year] ) && Data[Month] = 12 ) ) - CALCULATE ( SUM ( Data[Stock] ), FILTER ( Data, Data[Type d'article] = EARLIER ( Data[Type d'article] ) && Data[Year] = EARLIER ( Data[Year] ) + 1 ) ) )Result:
Here is demo ,please try it
Best Regards,
Lin
Hi v-lili6-msft
thanks for your help !
i appreciate it
Actually, the result should be :
January(2018) - December(2017)
February(2018) - December(2017)
Mars(2018) - December(2017)
excetera
and in 2019, the result should be :
January(2019) - December(2018)
February(2019) - December(2018)
Mars(2019) - December(2018)
excetera
So, for exemple, when i select " the month January " in the filter; i need to see the "stock value of January(2018) - stock value of december(2017).
Juste to make sur that the new code works well : For the "AAAA Sable", when we select 01/01/2018, the result should be : 25 687.44(stock value in January) -28 493.57(stock value in december) = -2 806.13.
I send u back the file with the small modification that i made to your code : ( i replaced the column "stock" with the colown "Val.Stck va". And also replaced "type d'article" with "groupe de marchandise". And i put the date in a filter.
https://drive.google.com/open?id=1CtnvMq3mpexlESyyVLjbhQZo6H12RmR2
Hope you can help me out
Hi@ Ghaston
After my research by your new file,You can use this measure
Column 2 = CALCULATE( SUM( Data[Val.stk va]), FILTER( Data, Data[Groupe marchandises]=EARLIER(Data[Groupe marchandises])&&Data[Year]=EARLIER(Data[Year])&&Data[Month]=EARLIER(Data[Month])))- CALCULATE( SUM( Data[Val.stk va]), FILTER( Data, Data[Groupe marchandises]=EARLIER(Data[Groupe marchandises])&&Data[Year]+1=EARLIER(Data[Year])&&Data[Month]=12))Result:
Here is demo, please try it
Best Regards,
Lin
Hi v-lili6-msft
Thanks for the help
Your solution works exactly as i wanted !
Best regards
Ghaston
