Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
  • Selva-Salimi's avatar
    Selva-Salimi
    1 year ago

    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])))
    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. 
Selva-Salimi's avatar
Selva-Salimi
Icon for Solution Sage rankSolution Sage
1 year ago

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])))
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. 
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    Selva-Salimi
    Icon for Solution Sage rankSolution Sage
    1 year ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        Selva-Salimi
        Icon for Solution Sage rankSolution Sage
        1 year ago

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