Forum Discussion
Helper IICalculate, 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()
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
FloriankxSolution Sage
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
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
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