Three Routes: YTD and Cumulative Sum

Relevant Scenario(s)

Data table

Technique in SQL

select  expense_date,
expense_amount,
sum(expense_amount)
over (partition by extract(year from expense_date)
order by expense_date)
from gla_expense;
NOTE THE TRANSITION FROM DECEMBER 31, 2020 to JANUARY 31 2021

Technique in Power BI

NOTE THE TRANSITION in YTD Expense FROM DECEMBER 31, 2019 to JANUARY 31 2020

Technique in Pandas

NOTE THE TRANSITION in YTD expense FROM DECEMBER 31, 2019 to JANUARY 31 2020

Setup data

=RANDBETWEEN(800, 1000)Since this is a small table, we can create a short script to insert values into the tables instead of importing the data.In the excel sheet, place the formulae:In column D= "INSERT INTO gla_expense (expense_date, expense_amount) VALUES ('"&TEXT(A1,"mm/dd/yyyy")&"', "&$B1&");"In column F= "INSERT INTO retail_sales (sales_date, sales_amount) VALUES ('"&TEXT(A1,"mm/dd/yyyy")&"', "&$B1&");"
INSERT INTO gla_expense (expense_date, expense_amount) VALUES ('01/31/2019', 815);
INSERT INTO gla_expense (expense_date, expense_amount) VALUES ('02/28/2019', 2236);
INSERT INTO gla_expense (expense_date, expense_amount) VALUES ('03/31/2019', 8980);
INSERT INTO retail_sales (sales_date, sales_amount) VALUES ('01/31/2019', 815);
INSERT INTO retail_sales (sales_date, sales_amount) VALUES ('02/28/2019', 2236);
INSERT INTO retail_sales (sales_date, sales_amount) VALUES ('03/31/2019', 8980);
CREATE TABLE public.gla_expense
(
expense_date date NOT NULL,
expense_amount numeric(10,2),
CONSTRAINT gla_expenses_pkey PRIMARY KEY (expense_date)
)
TABLESPACE pg_default;
CREATE TABLE public.retail_sales
(
sales_date date NOT NULL,
sales_amount numeric(10,2),
CONSTRAINT retail_sales_pkey PRIMARY KEY (sales_date)
)
TABLESPACE pg_default;
gla_expense_df = pd.read_csv(r'path of csv file')
retail_sales = pd.read_csv(r'path of csv file')

Certified Data Analyst (DA-100 Power BI), pursuing Masters in University Of Michigan’s Applied Data Science Program https://www.linkedin.com/in/simi-talkar/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store