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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBI5851
Helper V
Helper V

Incorrect Earlier calculation

Hi,

 I have to capture the count of Account for a specific region whose status is current = "Receive" but the value immediately prior to that should have been 'Call".  My data is as below. 

 

PrimaryIDAcctiDCategoryStartKeyValStatusSequence
J1A1001East1/1/2020OpenPrevious1
J2A1001East1/2/2020CallPrevious2
J3A1001East1/3/2020ReceiveCurrent3
J4B1001West1/2/2020OpenCurrent1
J5C1001West1/5/2020OpenPrevious1
J6C1001West1/6/2020CallCurrent2
J7D1001East1/1/2020OpenPrevious1
J8D1001East1/2/2020ReceiveCurrent2
J9F1001East1/5/2020CallPrevious1
J10F1001East1/7/2020ReceivePrevious2
J11F1001East1/8/2020OpenPrevious3
J12F1001East1/9/2020CallPrevious4
J13F1001East1/10/2020ReceiveCurrent5

 

So for the above table, my jan count that fits the requirement is 3 (A1001 on 1/3 and F1001 on 1/7 and F1001 on 1/10). 

 

I tried this DAX, but i keep getting an error with Earlier function. 

 

Rec_PreVal_Call = calculate(counta(AccHistory[AcctID]), filter (AccHistory, AccHistory[Category] = "East" && AccHistory[KeyVal] = "Receive" ), filter(AccHistory, AccHistory[Category] = "East" && AccHistory[KeyVal] = "Call" && Earlier(Keyval,1)))

 

Any recommendations on how to achieve this please. 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @PBI5851 ,

 

Try create a flag as below.

Column = 
var last_date = CALCULATE(MAX('Table'[Start]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]<EARLIER('Table'[Start])))
var last_key = CALCULATE(MAX('Table'[KeyVal]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]=last_date))
return
last_key

2.PNG

Then you should be able to count the id.

 

Best Regards,

Jay

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

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

Hi @PBI5851 ,

 

Try create a flag as below.

Column = 
var last_date = CALCULATE(MAX('Table'[Start]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]<EARLIER('Table'[Start])))
var last_key = CALCULATE(MAX('Table'[KeyVal]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]=last_date))
return
last_key

2.PNG

Then you should be able to count the id.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
OwenAuger
Super User
Super User

Hi @PBI5851 

 

Here's a measure that should work, if I've understood your requirements correctly:

Rec_PreVal_Call = 
SUMX (
    CALCULATETABLE ( 
        SUMMARIZE ( ACCHistory, ACCHistory[AcctiD], ACCHistory[Sequence] ),
        ACCHistory[KeyVal] = "Receive"
    ),
    VAR Seq = ACCHistory[Sequence]
    RETURN
    CALCULATE ( 
        INT ( SELECTEDVALUE ( ACCHistory[KeyVal] ) = "Call" ),
        ACCHistory[Sequence] = Seq - 1
    )
)

There are certainly alternative ways of writing the measure.

I avoided the EARLIER function in this case by using a variable for the current Sequence value (Seq).

 

Does this work in your model?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Owen,
 Thank you for the response. unfortunatly that will not work, as i cannot rely on the sequence field for this calculation, because there is a possibility for other Keyval  values to come into the table and the The Receive row can sometimes be repeated which will negate the seq-1 criteria. 

No problem, we just need some column to determine the order within the formula. Can we use the date in the Start column?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.