cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BachFel Regular Visitor
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
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.

View solution in original post

10 REPLIES 10
Floriankx Established Member
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
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
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
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
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
Regular Visitor

Re: Calculate, sum? need help with a formula

PBI.JPG

 

Unbenannt.JPG

 

Probably I still didn´t get it..

Floriankx Established Member
Established Member

Re: Calculate, sum? need help with a formula

Hello,

 

you wrote:

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

instead of

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

 

Please correct and let me know

BachFel Regular Visitor
Regular Visitor

Re: Calculate, sum? need help with a formula

Hello,

 

I wrote


 

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

instead of

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

 

 

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

 


 

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

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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