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

 

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?