Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Rob12
Regular Visitor

Converting Excel formula into DAX

Hi guys,

how can I translate the following formula in DAX?
I am still a beginner and I can not do it.
Can someone help me?

 

" =WENNFEHLER(INDEX(H:H;AGGREGAT(15;6;ZEILE($E$2:$E$82)/(LINKS($E$2:$E$82;3)="MXX");ZEILE(A1))-1);"") "

 

I am german and have written the formula logically also in german. I hope you can help me anyway.

 

Besistelldatum.JPG

I want to search for all workstations that start with "MXX..." in a column and always output the book date (in another column) of the preceding workstation.
MXX workstations are not all named the same but the string "MXX" is always the same.
MXX workstations can appear more than once in the column, but even if they are mentioned twice, they must always be considered individually.
The preceding work center is also always different in most cases.
I have marked the example lines in color.

 

Greetings, Rob

3 REPLIES 3
tamerj1
Super User
Super User

Hi @Rob12 

I wish if I speak German. 
how do you want to disply this result? Or you just want to create a new calculated table? I can ses that you have selected the date before the one that includes MXX. Any criteria for that? Can you provide the same sample data as copy/paste?  Thank you

The result should then be an extra column in the Power BI report and compared with the following.
Delivery date, statistical delivery date, date of invoice posting, date of goods receipt.
But these four dates are already in another query.
I can't get all the dates from one query because SAP and the interface don't allow it.

I will then link the dates to another query via column "D".

Is this somehow understandable?

Greetings, Rob

Hi @tamerj1,

it is about outputting the posting date of the work center which is before the "MXX..." work center.
See column "J".

"MXX" is a substring of the workstation which is always the same. However, the previous work center is not always the same. The date of the previous workstation is to be output, however, because this date is to be compared afterwards.
At the end, two dates are compared in order to trace a date fidelity.

 

you can find the table via the following link:

 

https://docs.google.com/spreadsheets/d/1PMVDsx0ZqIvp6gQklat6WNpxDTPzr5hi/edit?usp=sharing&ouid=10255...

 

I hope its working 🙂

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.