Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
THENNA_41
Post Partisan
Post Partisan

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 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @THENNA_41 
Here is a sample file with the solution https://www.dropbox.com/t/ukAb0PlQWRb6N4BA

2.png1.png

QTY = 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]

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

Hi @THENNA_41 
I hope this is what you need https://www.dropbox.com/t/HNgMILjEZ8o8ZmU3

1.png2.png

@tamerj1  thank you so much but i am getting single month multiple time sir. i want only one month 

Hi @THENNA_41 

please explain and clarify in details with screenshots and markups

@tamerj1   i am trying to change number to Million in Matrix visual but there is no option to change million format . i have created new measure and calcualted column both 

 

MILLION= FORMAT(SUM(template[Quantity]), "#,,.0M")
 
Measure = FORMAT(SUM(template[Quantity]), "#,,.0M")
 
So  i have added in that two measure and calcualted column its showing like  below 
 
THENNA_41_1-1658733072858.png

but i am using input sheet column its showing correct like below screen shot 

THENNA_41_2-1658733167345.png

 


 

THENNA_41
Post Partisan
Post Partisan

@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 

 

 

@THENNA_41 
Please check the following on your real data and let me know if it works or something else need to be done. 

1.png

@THENNA_41 
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

Please explain in details with screenshots

@tamerj1  sir i have added data set file in pbix for your  reference . https://drive.google.com/file/d/1FgeWDnnDAck1SMcH2-LDW-EAb_jnezRq/view?usp=sharing 

THENNA_41
Post Partisan
Post Partisan

@tamerj1  thank you  so much 

tamerj1
Super User
Super User

Hi @THENNA_41 
Here is a sample file with the solution https://www.dropbox.com/t/ukAb0PlQWRb6N4BA

2.png1.png

QTY = 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]

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors