Home » Analyse Data Using Financial Year

Analyse Data Using Financial Year

Analyse the sales data by Financial Year and Financial Quaters Assume the Financial year starts in April Dynamic array function allowed, but Extra marks for Legacy solutions or PowerQuery Solution

📌 Challenge Details and Links
Challenge Number: 20
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Analyse Data Using Financial Year with Power Query

Power Query solution 1 for Analyse Data Using Financial Year, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "TblSales"]}[Content], 
  StartMonth = 4, 
  Records = Table.AddColumn(
    Source, 
    "Records", 
    each [
      RD = Date.AddMonths([Date], - StartMonth + 1), 
      Q  = Text.From(Date.QuarterOfYear(RD)), 
      Y  = Date.Year(RD), 
      R  = [Quarter = Q, Year = Text.From(Y) & "-" & Text.End(Text.From(Y + 1), 2), Sales = [Sales]]
    ][R]
  ), 
  Table = Table.FromRecords(Records[Records]), 
  Sort = Table.Sort(Table, "Quarter"), 
  Return = Table.Pivot(Sort, List.Distinct(Sort[Quarter]), "Quarter", "Sales", List.Sum)
in
  Return
Power Query solution 2 for Analyse Data Using Financial Year, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "TblSales"]}[Content], 
  Quarter = Table.AddColumn(
    Source, 
    "Quarter", 
    each 
      let
        a = Date.QuarterOfYear([Date]) - 1, 
        b = if a = 0 then "4" else Text.From(a)
      in
        b
  ), 
  Year = Table.AddColumn(
    Quarter, 
    "Year", 
    each 
      let
        a = Date.Year([Date]), 
        b = if [Quarter] = "4" then a - 1 else a
      in
        b
  ), 
  Group = Table.Group(Year, {"Year", "Quarter"}, {{"A", each List.Sum([Sales])}}), 
  Pivot = Table.Pivot(Group, List.Distinct(Group[Quarter]), "Quarter", "A"), 
  Sol = Table.ReorderColumns(Pivot, {"Year"} & List.Sort(List.Skip(Table.ColumnNames(Pivot))))
in
  Sol
Power Query solution 3 for Analyse Data Using Financial Year, proposed by Brian Julius: 
let
 Source = Excel.CurrentWorkbook(){[Name="TblSales"]}[Content],
 AddFY = Table.AddColumn(Source, "Fiscal Year", each if Date.Month( [Date] ) < 4 then Date.Year( [Date] ) - 1 else Date.Year( [Date] )),
 AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each [
 a = Date.Month( [Date] ),
 b = if a < 4 then 4 else 
 if a < 7 then 1 else 
 if a < 10 then 2 else 3
 ][b]),
 RemoveCols = Table.RemoveColumns(AddFQ,{"Region", "Date"}),
 PivotAndSum = Table.Pivot(Table.TransformColumnTypes(RemoveCols, {{"Fiscal Quarter", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(RemoveCols, {{"Fiscal Quarter", type text}}, "en-US")[#"Fiscal Quarter"]), "Fiscal Quarter", "Sales", List.Sum),
 Reorder = Table.ReorderColumns(PivotAndSum,{"Fiscal Year", "1", "2", "3", "4"})
in
 Reorder

NOTE: If you use a robust date table in your data model (the best is Melissa de Korte's Extended Date Table), this problem basically solves itself with just the following DAX measure:

Total Sales = SUM( TblSales[Sales] )

Here's the link:

https://www.linkedin.com/posts/brianjuliusdc_powerbi-powerbiprotips-dax-activity-7083200706161041410-VfWI?utm_source=share&utm_medium=member_desktop
Power Query solution 4 for Analyse Data Using Financial Year, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "TblSales"]}[Content], 
  FiscalMonth = 4, 
  InsertedQuarter = Table.AddColumn(
    Source, 
    "FiscalQuarter", 
    each Number.RoundUp(Date.Month(Date.AddMonths([Date], - FiscalMonth + 1)) / 3)
  ), 
  InsertedYear = Table.AddColumn(
    InsertedQuarter, 
    "FiscalYear", 
    each Date.Year([Date]) - Number.From(Date.Month([Date]) < 4)
  )[[FiscalYear], [FiscalQuarter], [Sales]], 
  Sorted = Table.Sort(InsertedYear, {{"FiscalQuarter", 0}, {"FiscalYear", 0}}), 
  Output = Table.Pivot(
    Table.TransformColumnTypes(Sorted, {{"FiscalQuarter", type text}}), 
    List.Distinct(Table.TransformColumnTypes(Sorted, {{"FiscalQuarter", type text}})[FiscalQuarter]), 
    "FiscalQuarter", 
    "Sales", 
    List.Sum
  )
in
  Output
Power Query solution 5 for Analyse Data Using Financial Year, proposed by Owen Price: 
let
  Source = Excel.CurrentWorkbook(){[Name = "TblSales"]}[Content], 
  ChangeTypes = Table.TransformColumnTypes(
    Source, 
    {{"Date", type date}, {"Sales", Currency.Type}, {"Region", type text}}
  ), 
  AddFY = Table.AddColumn(
    ChangeTypes, 
    "FY", 
    each Date.Year([Date]) + (if Date.Month([Date]) > 3 then 0 else - 1)
  ), 
  AddQ = Table.AddColumn(
    AddFY, 
    "FQ", 
    each Text.From(Number.RoundUp(Date.Month(Date.AddMonths([Date], - 3)) / 3))
  ), 
  Group = Table.Group(AddQ, {"FY", "FQ"}, {{"Sales", each List.Sum([Sales])}}), 
  Pivot = Table.Pivot(Group, List.Distinct(List.Sort(Group[FQ])), "FQ", "Sales", List.Sum)
in
  Pivot
Power Query solution 6 for Analyse Data Using Financial Year, proposed by Alexandra Popoff: 
let
  Basis = Excel.CurrentWorkbook(){[Name = "Opt_FY_Basis"]}[Content][Column1]{0}, 
  Src = Table.TransformColumnTypes(
    Excel.CurrentWorkbook(){[Name = "DB"]}[Content], 
    {{"Region", type text}, {"Date", type date}, {"Sales", Currency.Type}}
  ), 
  Year = Table.AddColumn(
    Src, 
    "Year", 
    each Date.Year(Date.AddMonths([Date], - (Basis - 1))), 
    type number
  ), 
  Quarter = Table.AddColumn(
    Year, 
    "Quarter", 
    each Date.QuarterOfYear(Date.AddMonths([Date], - (Basis - 1))), 
    type number
  ), 
  Gp = Table.Group(
    Quarter, 
    {"Year", "Quarter"}, 
    {{"Total", each List.Sum([Sales]), type nullable number}}
  ), 
  Pivot = Table.Pivot(
    Table.TransformColumnTypes(Gp, {{"Quarter", type text}}, "fr-FR"), 
    List.Distinct({"1", "2", "3", "4"}), 
    "Quarter", 
    "Total", 
    List.Sum
  ), 
  Out_Sort = Table.Sort(Pivot, {{"Year", Order.Ascending}})
in
  Out_Sort
Power Query solution 7 for Analyse Data Using Financial Year, proposed by Amit Patel: 
let
  Source = Excel.CurrentWorkbook(){[Name = "TblSales"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Date", type date}, {"Sales", Int64.Type}, {"Region", type text}}
  ), 
  FY = Table.AddColumn(
    #"Changed Type", 
    "FisYear", 
    each if Date.Month([Date]) < 4 then Date.Year([Date]) - 1 else Date.Year([Date])
  ), 
  FQ = Table.AddColumn(
    FY, 
    "FisQuarter", 
    each Text.From(Number.RoundUp(Date.Month(Date.AddMonths([Date], - 3)) / 3))
  ), 
  Grouped = Table.Group(
    FQ, 
    {"FisYear", "FisQuarter"}, 
    {{"Sales", each List.Sum([Sales]), type nullable number}}
  ), 
  Pivot = Table.Pivot(
    Grouped, 
    List.Distinct(List.Sort(Grouped[FisQuarter])), 
    "FisQuarter", 
    "Sales", 
    List.Sum
  )
in
  Pivot

Solving the challenge of Analyse Data Using Financial Year with Excel

Excel solution 1 for Analyse Data Using Financial Year, proposed by Bo Rydobon 🇹🇭: 
=LET(
   d,
   EDATE(
       +C4:C735,
       -3),
   PIVOTBY(
       YEAR(
           d),
       ROUNDUP(
           MONTH(
           d)/3,
           ),
       D4:D735,
       SUM,
       ,
       0,
       ,
       0))
Excel solution 2 for Analyse Data Using Financial Year, proposed by 🇰🇷 Taeyong Shin: 
=SUM((YEAR(
   $C$4:$C$735) - (MOD(
   QUOTIENT(
       MONTH(
   $C$4:$C$735) + 8,
        3),
    4) + 1 = 4) & MOD(
   QUOTIENT(
       MONTH(
   $C$4:$C$735) + 8,
        3),
    4) + 1 = $F6 & G$5) * $D$4:$D$735)

365

=LET(
d,
    C4:C735,
   
q,
    MOD(
        QUOTIENT(
            MONTH(
                d) + 8,
             3),
         4) + 1,
   
PIVOTBY(YEAR(
                d) - (q = 4),
    q,
    D4:D735,
    SUM,
    ,
    0,
    ,
    0))
Excel solution 3 for Analyse Data Using Financial Year, proposed by Kris Jaganah: 
=LET(
   a,
   TblSales3[Date],
   b,
   INT(
       MONTH(
           a)/4),
   c,
   IF(
       b=0,
       4,
       b),
   d,
   YEAR(
           a),
   PIVOTBY(
       IF(
           c=4,
           d-1,
           d),
       c,
       TblSales3[Sales],
       SUM,
       0,
       0,
       ,
       0))
Excel solution 4 for Analyse Data Using Financial Year, proposed by Julian Poeltl: 
=LET(
   D,
   TblSales[Date],
   S,
   TblSales[Sales],
   Y,
   YEAR(
       D),
   M,
   MONTH(
       D),
   Q,
   IFS(
       M>9,
       3,
       M>6,
       2,
       M>3,
       1,
       1,
       4),
   YY,
   IF(
       M<4,
       Y-1,
       Y),
   UY,
   UNIQUE(
       YY),
   R,
   MAKEARRAY(
       COUNT(
           UY),
       4,
       LAMBDA(
           A,
           B,
           SUM(
               FILTER(
                   S,
                   YY&Q=INDEX(
                       UY,
                       A)&B,
                   0)))),
   VSTACK(
       HSTACK(
           "",
           "",
           "Financial Quarters",
           "",
           ""),
       HSTACK(
           VSTACK(
               "Financial Years",
               UY),
           VSTACK(
               SEQUENCE(
                   ,
                   4),
               IF(
                   R=0,
                   "",
                   R)))))
Excel solution 5 for Analyse Data Using Financial Year, proposed by Aditya Kumar Darak 🇮🇳: 
=SUMIFS(
   
    TblSales[Sales],
   
    TblSales[Date],
    ">=" & DATE(
        $G6,
         H$5 * 3 + 1,
         1),
   
    TblSales[Date],
    "<" & DATE(
        $G6,
         H$5 * 3 + 4,
         1)
   )
Excel solution 6 for Analyse Data Using Financial Year, proposed by Oscar Mendez Roca Farell: 
=LET(_d,
    C3:C734,
   _q,
    1+INT((MONTH(
        _d)-4)/3),
   _y,
    YEAR(
        _d)-(_q=0),
   _u,
    UNIQUE(
        _y),
    VSTACK(HSTACK(
        "Financial Years",
         {1,
        2,
        3,
        4}),
    HSTACK(_u,
    MAKEARRAY(ROWS(
        _u),
    4,
    LAMBDA(r,
    c,
    SUM(FILTER(D3:D734,
   (_y=INDEX(
       _u,
        r))*(IFS(
       _q,
       _q,
       1,
       4)=c),
    0)))))))
Excel solution 7 for Analyse Data Using Financial Year, proposed by Sunny Baggu: 
=MAKEARRAY(
ROWS(
    F6:F8),
   
4,
   
LAMBDA(r,
    c,
   
INDEX(
BYCOL(
(TblSales[Date] >= DATE(
    INDEX(
        F6:F8,
         r,
         ),
     SEQUENCE(
         ,
          4,
          4,
          3),
     1)) *
(
TblSales[Date] <
DATE(
    INDEX(
        F6:F8,
         r,
         ),
     SEQUENCE(
         ,
          4,
          7,
          3),
     1)) * TblSales[Sales],
   
LAMBDA(
    a,
     SUM(
         a))),
   
c)))
Excel solution 8 for Analyse Data Using Financial Year, proposed by Abdallah Ally: 
=LET(a,
   TblSales[Date],
   b,
   TblSales[Sales],
   c,
   ROUNDUP(
       MONTH(
           a)/3,
       0)-1,
    d,
   IF(c,
   YEAR(
           a)&c,
   (YEAR(
           a)-1)&4),
   e,
    SORT(
        UNIQUE(
            LEFT(
                d,
                4))),
    f,
   MAKEARRAY(COUNTA(
       e),
   4,
   LAMBDA(x,
   y,
   SUM(FILTER(b,
   d=(2020+x)&y,
   0)))) ,
   VSTACK(
       {"Financial Years",
       1,
       2,
       3,
       4},
       HSTACK(
           e,
           IF(
               f,
               TEXT(
                   f,
                   "0,0"),
               ""))))
Excel solution 9 for Analyse Data Using Financial Year, proposed by Gachuhi Muthoni: 
=IF(
   MONTH(
       [@Date])<4,
   YEAR(
       [@Date])-1,
   YEAR(
       [@Date]))
FQ=IF(
   MONTH(
       [@Date])<4,
    4,
    IF(
        MONTH(
       [@Date])<7,
         1,
         IF(
             MONTH(
       [@Date])<10,
             2,
             IF(
                 MONTH(
       [@Date])>9,
                 3))))

Solving the challenge of Analyse Data Using Financial Year with Python in Excel

Python in Excel solution 1 for Analyse Data Using Financial Year, proposed by Abdallah Ally: 
import pandas as pd
from math import ceil
file_path = 'Easy Excel Challenge 24th March.xlsx'
df = pd.read_excel(file_path, usecols='B:D', skiprows=2)
# Create a function to add custom year and quarter columns
def custom_year_quarter(col):
 q = ceil(col.month / 3)
 y = col.year
 if q < 2: return y - 1, 4
 else: return y, q - 1
# Data transformation and cleansing
df[['Year', 'Quarter']] = df['Date'].apply(custom_year_quarter).tolist()
df = df.groupby(['Year', 'Quarter'])['Sales'].sum().reset_index()
df['Sales'] = df['Sales'].map(lambda x: f'{x:,}')
df = df.pivot(index='Year', columns='Quarter', values='Sales').reset_index()
df.columns.name = None
df.rename(columns={'Year': 'Financial Years'}, inplace=True)
df.replace(float('nan'), '', inplace=True)
print(f"n{' '*30}Financial Quartersn{df}")
Python in Excel solution 2 for Analyse Data Using Financial Year, proposed by Owen Price: 
df = xl("TblSales[
#All]", headers=True)
df['fy'] = df.Date.dt.year - np.where(df.Date.dt.month > 3, 0, 1)
df['fq'] = np.ceil((df.Date - pd.DateOffset(months=3)).dt.month / 3)
(df.pivot_table(values='Sales', 
 index='fy', 
 columns='fq', 
 aggfunc=np.sum, 
 fill_value=0)
 .rename_axis(index='', columns=None)
 .reset_index())

Leave a Reply