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.
are you sure that you have created a column, not measure??
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.
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.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!