Forum Discussion
Calculate Quarterly Data from Cumulative Data
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.
Anonymous
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])))returnsum(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.
9 Replies
- Selva-Salimi
Solution Sage
Anonymous
would you share dummy samples of your data and your expectations?
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 https://www.dropbox.com/scl/fi/308fuw9115b9t045k0g5y/Practice-03_Many-Securities_Many-Accounts.xlsx?rlkey=rxvvcurq8iwgesvh1bfdkl23a&st=8otizyz0&dl=0
https://www.dropbox.com/scl/fi/sgm5o0c23cr87wwub0c45/Practice-03_Many-Securities_Many-Accounts.pbix?rlkey=vzf41se07ih6dmawam8356zi4&st=ybvsapf2&dl=0
Thanks- Selva-Salimi
Hi Anonymous
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])))returnIncomeStatement[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.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??