Calculate Quarterly Data from Cumulative Data

[ThangNT]

I have three data tables: Date, Accounts, and IncomeStatement. The Income Statement table contains cumulative data for three companies—FPT, HPG, and PNJ—specifically for two accounts: Gross Sales and Deductions. I need to calculate quarterly data for each account and each company based on this semi-annual, nine-month, and annual cumulative data.
I have tried to used SWITCH TRUE function but it doesn't work.

[Selva-Salimi]

Hi @ThangNT 

You can write a column in incomestatement table as follows:

Quarterly_Amount = var prev_Q=calculate(max(IncomeStatement[Date Key]) , filter(IncomeStatement , IncomeStatement[Date Key] < EARLIER(IncomeStatement[Date Key])))

return

IncomeStatement[Amount] - CALCULATE(max(IncomeStatement[Amount]) , filter(IncomeStatement , IncomeStatement[Date Key]=prev_Q && IncomeStatement[Security Key]=EARLIER(IncomeStatement[Security Key]) && IncomeStatement[Account Key] = EARLIER(IncomeStatement[Account Key])))

*let me know is it exactly what you expect to want to reset per year?!

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[ThangNT]

I have received the error message "EARLIER/EARLIEST refers to an earlier row context which doesn't exist.".  I want that quarterly amount will be reset per year, meaning that, the second quarter 2015 values will be the semi-annual 2015 values subtracting the first quarter values.
In additonal, I want to use a measure instead of a calculated column.

[Selva-Salimi]

are you sure that you have created a column, not measure??

[ThangNT]

When I use your DAX formula for a calculated column, the error message "EARLIER/EARLIEST refers to an earlier row context which doesn't exist." disappears.  However, the quarterly values for 2015 are incorrect.

[Selva-Salimi · Accepted solution]

@ThangNT 

if you want to write a measure, you can try this measure:

Quarterly_Amount_measure = var prev_Q=calculate(max('Date'[Date Key]) , filter(all('Date'[Date Key]) , 'Date'[Date Key] < selectedvalue('Date'[Date Key])))

return

sum(IncomeStatement[Amount]) - CALCULATE(sum(IncomeStatement[Amount]) , filter(all(IncomeStatement) , IncomeStatement[Date Key]=prev_Q && IncomeStatement[Security Key]=SELECTEDVALUE(IncomeStatement[Security Key]) && IncomeStatement[Account Key] in values(Accounts[Account Key]) && year(IncomeStatement[Date Key])=selectedvalue('Date'[Year]) ))

keep in mind that rows and columns in matrix should be selected from Account and Date table.

let me know if this is your expectation.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[ThangNT]

It feels like magic! This problem had been getting on my nerves for days, and I truly appreciate your help. Thank you so much again!

[ThangNT]

https://www.dropbox.com/scl/fi/308fuw9115b9t045k0g5y/Practice-03_Many-Securities_Many-Accounts.xlsx?...
https://www.dropbox.com/scl/fi/sgm5o0c23cr87wwub0c45/Practice-03_Many-Securities_Many-Accounts.pbix?...
Thanks

[ThangNT]

Date Key	Year	Quarter
03-31-14	2014	Q1
06-30-14	2014	Q2
09-30-14	2014	Q3
12-31-14	2014	Q4
03-31-15	2015	Q1
06-30-15	2015	Q2
09-30-15	2015	Q3
12-31-15	2015	Q4

Account Key	Class	Subclass
1	Net Sales	Gross Sales
2	Net Sales	Deduction

Security Key	Industry Key	Date Key	Account Key	Amount
PNJ	255040	03-31-14	1	2,476,645,830,566
PNJ	255040	06-30-14	1	4,968,075,298,030
PNJ	255040	09-30-14	1	7,340,661,939,448
PNJ	255040	12-31-14	1	9,297,810,872,565
PNJ	255040	03-31-14	2	-22,954,574,987
PNJ	255040	06-30-14	2	-43,476,519,121
PNJ	255040	09-30-14	2	-68,887,703,362
PNJ	255040	12-31-14	2	-98,592,775,013
PNJ	255040	03-31-15	1	2,143,708,017,008
PNJ	255040	06-30-15	1	3,868,684,344,728
PNJ	255040	09-30-15	1	5,672,524,928,345
PNJ	255040	12-31-15	1	7,741,445,592,122
PNJ	255040	03-31-15	2	-6,645,160,137
PNJ	255040	06-30-15	2	-13,778,057,891
PNJ	255040	09-30-15	2	-22,386,483,710
PNJ	255040	12-31-15	2	-33,092,915,485
FPT	451030	03-31-14	1	10,867,321,675,354
FPT	451030	06-30-14	1	14,241,494,214,006
FPT	451030	09-30-14	1	35,029,947,594,626
FPT	451030	12-31-14	1	32,873,026,689,995
FPT	451030	03-31-14	2	-49,685,343,347
FPT	451030	06-30-14	2	-97,644,480,042
FPT	451030	09-30-14	2	-156,981,881,776
FPT	451030	12-31-14	2	-228,370,331,060
FPT	451030	03-31-15	1	32,873,026,689,955
FPT	451030	06-30-15	1	18,237,232,496,581
FPT	451030	09-30-15	1	27,966,213,261,357
FPT	451030	12-31-15	1	38,707,143,264,487
FPT	451030	03-31-15	2	0
FPT	451030	06-30-15	2	-267,211,130,394
FPT	451030	09-30-15	2	-468,728,388,594
FPT	451030	12-31-15	2	-747,444,508,465
HPG	151020	03-31-14	1	6,576,317,290,406
HPG	151020	06-30-14	1	13,338,718,385,687
HPG	151020	09-30-14	1	19,166,119,243,978
HPG	151020	12-31-14	1	25,851,816,458,125
HPG	151020	03-31-14	2	-60,818,402,801
HPG	151020	06-30-14	2	-141,951,551,508
HPG	151020	09-30-14	2	-222,492,959,708
HPG	151020	12-31-14	2	-326,467,635,412
HPG	151020	03-31-15	1	5,927,283,729,062
HPG	151020	06-30-15	1	7,740,367,029,589
HPG	151020	09-30-15	1	6,948,179,608,452
HPG	151020	12-31-15	1	7,248,728,069,861
HPG	151020	03-31-15	2	-89,347,814,331
HPG	151020	06-30-15	2	-92,600,588,468
HPG	151020	09-30-15	2	-103,122,219,506
HPG	151020	12-31-15	2	-126,555,700,326

[Selva-Salimi]

@ThangNT 

would you share dummy samples of your data and your expectations?