cancel
Showing results for
Did you mean:
BachFel 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
Floriankx 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
Floriankx 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.

BachFel 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

Floriankx 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

BachFel 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

Floriankx 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.

BachFel Regular Visitor

## Re: Calculate, sum? need help with a formula  Probably I still didn´t get it..

Floriankx 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

BachFel 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.

Floriankx 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   