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
Anonymous
Not applicable

Last transaction dynamically

Hello!

I'm trying to find the lastest transaction in a dynamic period, I have checked another posts in the forum but they doesnt work.

I have tried everything and I can get them but the dynamic calculation is so slow like 2 min aprox.

I'll let here a example of my problem:

Data:

IDDate (DD/MM/YYYY)Type
110/10/2020A
108/12/2020B
212/11/2020A

Results expected:

**First example (Filter: 01/01/2019-31/11/2020)

TypeCount
A2
Total2

 

**Second example (Filter: 01/01/2019-31/12/2020)

TypeCount
A1
B1
Total2

 

If someone can help, i'll be really happy!

 

Thank you!!!

1 ACCEPTED SOLUTION

Hi @Anonymous ,

Try the following formula:

Max_Date = 
CALCULATE(
    MAX('Table'[Date]),
    ALLSELECTED('Table'),
    GROUPBY('Table','Table'[ID])
)
Measure = 
var result = 
    CALCULATE(
        COUNT('Table'[ID]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Date] = [Max_Date]
            && 'Table'[Type] = MAX('Table'[Type])
        )
    )
return 
    IF(
        HASONEFILTER('Table'[Type]),
        result,
        DISTINCTCOUNT('Table'[ID])
    )

image.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

13 REPLIES 13
v-kkf-msft
Community Support
Community Support

Hi @Anonymous ,

Try the following formula:

Measure = 
var MaxDate = 
    CALCULATE(
        MAX('Table'[Date]),
        ALLSELECTED('Table'),
        GROUPBY('Table','Table'[ID])
    )
return 
    CALCULATE(
        COUNT('Table'[Type]),
        GROUPBY(
            FILTER(
                ALLSELECTED('Table'),
                'Table'[Date] = MaxDate
            ),
            'Table'[Type]
        )
    )

image.pngimage.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thank you!!

I see a problem in your measure, the first example is wrong because the total should be 2, and it shows 1.

JLMR10_0-1618209445062.png

 

Hi @Anonymous ,

Try the following formula:

 

Measure = 
var MaxDate = 
    CALCULATE(
        MAX('Table'[Date]),
        ALLSELECTED('Table'),
        GROUPBY('Table','Table'[ID])
    )
var result = 
    CALCULATE(
        COUNT('Table'[Type]),
        GROUPBY(
            FILTER(
                ALLSELECTED('Table'),
                'Table'[Date] = MaxDate
            ),
            'Table'[Type]
        )
    )
return 
    IF(
        HASONEFILTER('Table'[Type]),
        result,
        DISTINCTCOUNT('Table'[ID])
    )

 

image.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Your measure is amazing but I cant get it to work, with my real data i get a ID in 2 differents groups :S The example that i'm analyzing right now to check the measure is:

IDDate (DD/MM/YYYY)Type
106/03/2020A
102/03/2020A
119/12/2019B

 

Hi @Anonymous ,

Could you describe in detail what went wrong? What I understand is that there is only one latest status for each ID during the selected period. Therefore, the Total part is the total number of id in the time period.

 

Best Regards,
Winniz

Anonymous
Not applicable

Exactly, you described it perfectly. But with your measure i'm getting 2 transactions for a ID in the selected period

Hi @Anonymous ,

Try the following formula:

Max_Date = 
CALCULATE(
    MAX('Table'[Date]),
    ALLSELECTED('Table'),
    GROUPBY('Table','Table'[ID])
)
Measure = 
var result = 
    CALCULATE(
        COUNT('Table'[ID]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Date] = [Max_Date]
            && 'Table'[Type] = MAX('Table'[Type])
        )
    )
return 
    IF(
        HASONEFILTER('Table'[Type]),
        result,
        DISTINCTCOUNT('Table'[ID])
    )

image.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

This measures works perfectly fine!! I just got another problem, i have got a subtype for each type and i cannot count them correctly if i add the subtype to the measure.

Anonymous
Not applicable

I got it!

Measure = 
var resultType = 
    CALCULATE(
        COUNT('Table'[ID]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Date] = [Max_Date]
            && 'Table'[Type] = MAX('Table'[Type])
        )
    )
var resultSubType = 
    CALCULATE(
        COUNT('Table'[ID]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Date] = [Max_Date]
            && 'Table'[Type] = MAX('Table'[Type])
            && 'Table'[SubType] = MAX('Table'[SubType])
        )
    )
return 
    IF(
        HASONEFILTER('Table'[Type]),
        IF(
            HASONEFILTER('Table'[SubType]),
            resultSubType,
            resultType
        ),
        DISTINCTCOUNT('Table'[ID])
    )
Arentir
Resolver III
Resolver III

Hi @Anonymous ,

I didn't quite understand the result expected example part. From the problem description I understand you want to show :

In case all dates are selected

IDDate (DD/MM/YYYY)Type
108/12/2020B

 

In calse 01/01/2020 to 30/11/2020 is selected

IDDate (DD/MM/YYYY)Type
212/11/2020A

 

is that correct?

Anonymous
Not applicable

I want to know the count of each type for the lastest transaction of a certain period of time, the period will be a slicer, so it needs to be dynamic.

 

In case all date are selected the lastest transaction for each ID are the ones that you said.

amitchandak
Super User
Super User

@Anonymous , Try a measure like

 

Status =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( count('Table'[Status] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[Date] = __date )

Anonymous
Not applicable

Unfortunatelly, It doesnt seems to work, every type have value 1 with that measure.

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.