cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate, sum? need help with a formula

Following problem:

in the table LN[Leistungsbeschreibung] can be more than one row with the text "Objektbegehung", per ID.

i.E.:

ID          Leistungsbeschreibung       Datum

111        Objektbegehung                 20.03.2018

222        abcd                                    14.01.2018

111        Objektbegehung                 01.01.2018

I want to create a formula in another table KontElement[Datum]  (where all ID´s are listed once). Output should be:

ID      Datum

111    01.01.2018

222    Blank

Only the earliest date (per ID) should be listed. If the text is not "Leistungsbeschreibung" ist should be Blank()

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Calculate, sum? need help with a formula

Hello,

I used ID instead of KostElementID, might be a little confusing because you have several IDs

FILTER(LN;LN[KostElementID]=EARLIER(KontElement[KostElementID]) hopefully works now.

10 REPLIES 10
Established Member

## Re: Calculate, sum? need help with a formula

Hello I don't get your point exactly.

Why is in your example the 222 Blank?

If you want to do it with DAX try the following:

CALCULATE(Min(LN[Datum]);FILTER(LN;LN[Leistungsbeschreibung)="Objektbegehung")))

So you get the earliest for each ID where Leistungsbeschreibung = Objektbehung.

I hope I understood you correctly.

Regular Visitor

## Re: Calculate, sum? need help with a formula

@Floriankx wrote:

Hello I don't get your point exactly.

Why is in your example the 222 Blank?

If you want to do it with DAX try the following:

CALCULATE(Min(LN[Datum]);FILTER(LN;LN[Leistungsbeschreibung)="Objektbegehung")))

So you get the earliest for each ID where Leistungsbeschreibung = Objektbehung.

I hope I understood you correctly.

Hello, the ID 222 should be blank because the text is not "Objektbegehung".

When I use your DAX formula, the output is that for every ID there is the same date (it is the earliest date with the text "Objektbegehung".

By the way: the formula also needs a filter for table KontElement [mandatID]=1.

Hope you can help

Established Member

## Re: Calculate, sum? need help with a formula

Hello,

you're right. Formula was incomplete:

=CALCULATE(MIN(LN[Datum]);FILTER(LN;LN[Leistung...]="Objektbegehung");FILTER(LN;LN[ID]=EARLIER(KontElement[ID])))

Mit freundlichen Grüßen

Best regards

Regular Visitor

## Re: Calculate, sum? need help with a formula

@Floriankx wrote:

Hello,

you're right. Formula was incomplete:

=CALCULATE(MIN(LN[Datum]);FILTER(LN;LN[Leistung...]="Objektbegehung");FILTER(LN;LN[ID]=EARLIER(KontElement[ID])))

Mit freundlichen Grüßen

Best regards

Hello,

I didn´t get it.

Until =CALCULATE(MIN(LN[Datum]);FILTER(LN;LN[Leistung...]="Objektbegehung"); everthing is clear to me.

Can u please forget the second filter (mandatID) i mentioned before and built the formula with the new expression earlier?

in hope to understand it and thank u a lot

Established Member

## Re: Calculate, sum? need help with a formula

Hello,

FILTER(LN;LN[ID]=EARLIER(KontElement[ID])

Earlier works in row context. It takes the ID of the current row in LN and compares it to the IDs of KontElement and so filters all the IDs which are similar to the current ID (also order of the formular suggests upside down). The second filter eliminates all except Objektbegehung and the MIN Term gives you the earliest date.

Regular Visitor

## Re: Calculate, sum? need help with a formula

Probably I still didn´t get it..

Established Member

## Re: Calculate, sum? need help with a formula

Hello,

you wrote:

FILTER(LN;KontElement[ID]=EARLIER(KontElement[ID])

FILTER(LN;LN[ID]=EARLIER(KontElement[ID])

Please correct and let me know

Regular Visitor

## Re: Calculate, sum? need help with a formula

Hello,

I wrote

FILTER(LN;KontElement[ID]=EARLIER(KontElement[ID])

FILTER(LN;LN[ID]=EARLIER(KontElement[ID])

because in the table LN there is now column with the expression you took.

Established Member

## Re: Calculate, sum? need help with a formula

Hello,

I used ID instead of KostElementID, might be a little confusing because you have several IDs

FILTER(LN;LN[KostElementID]=EARLIER(KontElement[KostElementID]) hopefully works now.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,020)