Forum Discussion
Month On Month Comparison between two sheets for current month value
I Have two excel which i have imported into Power bi . one table name called FINAL another table name called Template.
FINAL
Month&Year Plant Quantity
June 2022 ITGH 34 -- taking this value compare to all the months in template sheet .
July 2022 CH01 33
August 2022 CZ01 45
September 2022 RZ01 56
October 2022 LT01 34
November 2022 RS7Z 65
December 2022 DE19 23
January 2023 ITGF 67
Feburary 2023 NL07 78
March 2023 PLO2 23
I taking to July month quanity Sum from Final sheet this value compare to all months in Template sheet
Template
Month&Year Plant Quantity
June 2022 ITGH 24
July 2022 CH01 53
August 2022 CZ01 65
September 2022 RZ01 26
October 2022 LT01 44
November 2022 RS7Z 75
December 2022 DE19 33
January 2023 ITGF 87
Feburary 2023 NL07 58
March 2023 PLO2 23
Final Result :
Month&Year Plant Quantity July val finalsheet increase /Decrease
June 2022 ITGH 24 34 -10
July 2022 CH01 53 34 19
August 2022 CZ01 65 34 31
September 2022 RZ01 26 34 -8
October 2022 LT01 44 34 10
November 2022 RS7Z 75 34 31
December 2022 DE19 33 34 -1
January 2023 ITGF 87 34 53
Feburary 2023 NL07 58 34 24
March 2023 PLO2 23 34 -11
How to compare month on month FINAL sheet june value to all the months in template sheet .
looking for support . thanks in advance
Hi Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/ukAb0PlQWRb6N4BAQTY = SUM ( Template[Quantity] )July val finalsheet = SUMX ( VALUES ( Template[Month& Year] ), CALCULATE ( SUM ( FINAL[Quantity] ), FINAL[Month& Year] = MIN ( FINAL[Month& Year] ) ) )increase/Decrease = [QTY] - [July val finalsheet]
12 Replies
- tamerj1
Community Champion
Hi Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/ukAb0PlQWRb6N4BAQTY = SUM ( Template[Quantity] )July val finalsheet = SUMX ( VALUES ( Template[Month& Year] ), CALCULATE ( SUM ( FINAL[Quantity] ), FINAL[Month& Year] = MIN ( FINAL[Month& Year] ) ) )increase/Decrease = [QTY] - [July val finalsheet] tamerj1 thank you so much
tamerj1 one more question . how to change it to month on month comparision instead july month .compare month on month compare like below
July - july
August - August
- tamerj1
Anonymous
Yes can be done. But I have a question; in your sample data there is only one plant for each month. Is this the case in the real data?tamerj1 Sir there is multiple plant for each month sir. after made the relatioship between two table date wise working afte add plant one sheet quanity its showing same value for all month
- tamerj1
Anonymous
Please check the following on your real data and let me know if it works or something else need to be done.
- tamerj1
Hi Anonymous
I hope this is what you need https://www.dropbox.com/t/HNgMILjEZ8o8ZmU3