Forum Discussion

brotha_lee's avatar
brotha_lee
Regular Visitor
8 years ago
Solved

SUMIF / DAX

Hi, I am new to DAX but I am struggling with a very simple question. I have the following table: AccountingPeriod Project Amount 201801 X 10 201802 X -10 201803   20 201803 ...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Here is the result i got.  You may download the PBI file from here.

     

    Hope this helps.

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi

 

This will help you.

 

Proj_total = CALCULATE(SUM(Ex_Over[Amount]), FILTER(Ex_Over,Ex_Over[Project]=EARLIER(Ex_Over[Project])))

 

Thanks
Raj

brotha_lee's avatar
brotha_lee
Regular Visitor
8 years ago

Thanks for the response however I get the following error when using the formula:

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

How can I solve this / Am I doing something wrong?

 

Here is my formula with the table names: 

PROJ_TOTAL = CALCULATE(SUM('ENT_SCD rptGLBalance'[Amount]);FILTER('ENT_SCD rptGLBalance';'ENT_SCD rptGLBalance'[Project]=EARLIER('ENT_SCD rptGLBalance'[Project])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

     

    brotha_lee wrote:

    Thanks for the response however I get the following error when using the formula:

    EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

    How can I solve this / Am I doing something wrong?

     

    Here is my formula with the table names: 

    PROJ_TOTAL = CALCULATE(SUM('ENT_SCD rptGLBalance'[Amount]);FILTER('ENT_SCD rptGLBalance';'ENT_SCD rptGLBalance'[Project]=EARLIER('ENT_SCD rptGLBalance'[Project])))


    Create as a calculated column, not as measure. You will not get the error.

     

    Thanks
    Raj

    • brotha_lee's avatar
      brotha_lee
      Regular Visitor
      8 years ago

      Thanks that did the trick, however I now have the following issue,. I have a measure which calculates the YTD values based on the following formula:

      YTD = round(CALCULATE('sum(ENT_SCD rptGLBalance'[amount]);filter(all('ENT_SCD dimAccountingPeriods'[AccountingPeriodKey]);'ENT_SCD dimAccountingPeriods'[AccountingPeriodKey]<=Max('ENT_SCD dimAccountingPeriods'[AccountingPeriodKey])));0).

       

      when I use this measure in the column like:

      PROJ_TOTAL = CALCULATE('ENT_SCD rptGLBalance'[YTD])FILTER('ENT_SCD rptGLBalance';'ENT_SCD rptGLBalance'[Project]=EARLIER('ENT_SCD rptGLBalance'[Project])))

      it give me funny results. For example when I select accountingperiod "201802" the result is as follows:

      AccountingPeriodProjectAmountYTDPROJ_TOTAL
      201801X1010 
      201802X-10-10-10

       

      It looks like the PROJ_TOTAL is only calculating for the selected accountingperiod. Can this be solved?