Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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

 

Capture.PNG

 

Thanks,

Yasaui

1 ACCEPTED SOLUTION

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

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
PattemManohar
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

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

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

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi, @v-cherch-msft!

 

I do not not why, but your formula with EARLIER works perfectly fine. Probably, in my formula it had problems with IF or something else.

 

However, your formula was not what I asked for. I needed to calculate the difference between two quarters, but not the sum of all preceding quarters. Anyway, I was able to adapt your formula to my needs.

 

Thanks a lot!

 

Regards,

Yassaui

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.