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...
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.
| 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 |