Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

EARLIER function not working

I have the following problem:

 

There is a table column with quarterly metreage called Total. However, it is reported not quarterly but as YTD (Q2 will report Q1+Q2, Q3 will report everything since Q1 tilll Q3). I want to calculate the real metreage for each quarter.

 

The logic is to create a separate column for each quarter, and, for example,  for each row in Q2 to calculate new values. This means that for each row I should get the Field name and then find out the same field name in Q1 and get the difference between Q2 and Q1 totals.

 

On the forums most of the time people recommend EARLIER function to get the value from the current row to make search. However, it is not working for me. Could anyone please help or suggest another solution to my problem?

 

Here is the formula (it is not fuinished yet, but I get error in EARLIER):

 

Total adj = IF(LEFT(Drilling[Quarter],2)="Q1",Drilling[Total],IF(LEFT(Drilling[Quarter],2)="Q2",CALCULATE(MAX(ALL(Drilling[Total]),FILTER(Drilling,Drilling[Field] = EARLIER(Drilling[Total adj])))),0))

 

 

Thanks,

Yasaui

Need Help
Show and Tell
Tips and Tricks
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    You may refer to below measure. Please let me know if it matches your request or not.

    Year = RIGHT(Drilling[Quarter],4)
    total2 =
CALCULATE (
    SUM ( Drilling[Development] ),
    FILTER (
        Drilling,
        Drilling[Year] = EARLIER ( Drilling[Year] )
            && Drilling[Quarter] <= EARLIER ( Drilling[Quarter] )
    )
)

     

    Regards,

    Cherie

4 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Anonymous Please post the same data (test data) in copiable format to replicate your issue and will be helpful to suggest any solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Is this one okay?

       

      CompanyFieldTotalExploration/AppraisalDevelopmentQuarterSales Account

      UzenmunaygazUzen49,383049,383Q1 2018UZENMUNAYGAS
      UzenmunaygazUzen114,1730114,173Q2 2018UZENMUNAYGAS
      UzenmunaygazUzen178,7460178,746Q3 2018UZENMUNAYGAS
      UzenmunaygazUzen184,9080184,908Q4 2017UZENMUNAYGAS
      UzenmunaygazUzen139,6320139,632Q3 2017UZENMUNAYGAS
      UzenmunaygazUzen94,355094,355Q2 2017UZENMUNAYGAS
      UzenmunaygazUzen43,720043,720Q1 2017UZENMUNAYGAS
      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous

         

        You may refer to below measure. Please let me know if it matches your request or not.

        Year = RIGHT(Drilling[Quarter],4)
        total2 =
CALCULATE (
    SUM ( Drilling[Development] ),
    FILTER (
        Drilling,
        Drilling[Year] = EARLIER ( Drilling[Year] )
            && Drilling[Quarter] <= EARLIER ( Drilling[Quarter] )
    )
)

         

        Regards,

        Cherie