How to add last year and current year

Options
tz9lawa
tz9lawa Member, ALL USERS Posts: 5 New Contributor
in Planning & Modeling

Hi

I have monthly data by segment by product family. - Refer to document attached

 

I would like to build dashboard to display last year, current year and variance column.

tz9lawa_1-1595814122679.png

 

Therefore, i build 1 module, source, last year, current year and variance line item

tz9lawa_2-1595814169835.png

Question:-

How to input formula to grab data for FY20?

How to input formula to grab data for FY21?

 

Beside year, i also need half year data

  FY21FY20Variance
  1H2H  
  AUDmAUDmAUDm%
APA    
 PB    
 PC    
BPA    
 PB    
 PC    
CPA    
 PB    
 PC    
Tagged:

Comments

  • tz9lawa
    tz9lawa Member, ALL USERS Posts: 5 New Contributor

    thank you for your advice.

     

    Without formula added to last year and current year, how to calculate variances?

     

    thanks

  • tz9lawa
    tz9lawa Member, ALL USERS Posts: 5 New Contributor

    as per advice, i manage to build module to cater current year and prior year data.

    tz9lawa_0-1595917461249.png

    Using subset.

    with this blueprint setting, are we able to use formula to calculate column Volume AUDm and CM/t AUDm?  

     
     FY21 vs. FY20                  
      Contribution Margin    Volume     CM/t      
                        
      FY21FY20Variance FY21FY20Variance FY21FY20Variance  
                        
    SegmentProduct FamilyAUDmAUDmAUDm% ktktkt%AUDm AUD/tAUD/tAUD/t%AUDm  
    APA2012+8.0+67% 81801+1%+0.14 246149+97+65%+8.4  
     PB1535-20.0-57% 20182+12%+4.12 7672,000-1,233-62%-24.1  
     PC1612+4.0+33% 3941-2-4%-0.45 410296+114+39%+4.5  
    BPA8152+29.0+56% 23220+2%+0.95 3,5752,337+1,238+53%+28.0  
     PB2335-12.0-34% 81801+1%+0.41 283436-153-35%-12.4  
     PC6514+51.0+364% 20182+12%+1.65 3,322800+2,523+315%+49.4  
    CPA8532+53.0+166% 3941-2-4%-1.21 2,180790+1,390+176%+54.2  
     PB1334-21.0-62% 23220+2%+0.62 5741,528-954-62%-21.6  
     PC6552+13.0+25% 81801+1%+0.61 800648+153+24%+12.4  
                        
               Variance Kt * FY20 AUD/T     Volume FY21kt * Variance AUD/t
                        
     

     

     

     
     

     

     

     

Categories