cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

Thanks,

Yasaui

1 ACCEPTED SOLUTION
Employee

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

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.
4 REPLIES 4
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.

Proud to be a PBI Community Champion

Anonymous
Not applicable

Is this one okay?

CompanyFieldTotalExploration/AppraisalDevelopmentQuarterSales Account

 Uzenmunaygaz Uzen 49,383 0 49,383 Q1 2018 UZENMUNAYGAS Uzenmunaygaz Uzen 114,173 0 114,173 Q2 2018 UZENMUNAYGAS Uzenmunaygaz Uzen 178,746 0 178,746 Q3 2018 UZENMUNAYGAS Uzenmunaygaz Uzen 184,908 0 184,908 Q4 2017 UZENMUNAYGAS Uzenmunaygaz Uzen 139,632 0 139,632 Q3 2017 UZENMUNAYGAS Uzenmunaygaz Uzen 94,355 0 94,355 Q2 2017 UZENMUNAYGAS Uzenmunaygaz Uzen 43,720 0 43,720 Q1 2017 UZENMUNAYGAS
Employee

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

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors