Is there an expert for DAX on this forum? I do have a problem with data I need to analyze with DAX in PowerBI.
Please let me know if you are willing to take a look at my problem.
Since chances are high that there is none on this forum, I save myself to post the problem right now. But I”m happy to share I someone is interested in this challenge.
added 20th July:
This is the problem:
A system produces for each change in a database a row of data.
The columns are "Save date", "Item", "Activity" and "Plan Date"
- Save Date is the date the change was entered in the db.
- Item is a unique Part Name
- Activity is a Name of a specific action
- Plan date is the date the activity is planed to happen. It can be empty, in future or past, relative to save date.
Sample Data with 3 items (also in attached file):
Save Date Item Activity Plan Date
01.02.2020 A PPAP 01.05.2020
01.01.2020 A Receive
05.01.2020 A Receive 02.01.2020
09.01.2020 A Receive 11.01.2020
13.01.2020 A Receive 11.01.2020
15.01.2020 A Receive 14.01.2020
18.01.2020 A Receive 02.02.2020
20.01.2020 A Receive 17.01.2020
01.02.2020 A Send
01.02.2020 B PPAP 01.05.2020
01.01.2020 B Receive
05.01.2020 B Receive 03.01.2020
09.01.2020 B Receive 12.01.2020
13.01.2020 B Receive 12.01.2020
15.01.2020 B Receive 15.01.2020
18.01.2020 B Receive 02.02.2020
20.01.2020 B Receive 17.01.2020
01.02.2020 B Send
01.02.2020 C PPAP 01.05.2020
01.01.2020 C Receive
05.01.2020 C Receive 04.01.2020
09.01.2020 C Receive 13.01.2020
13.01.2020 C Receive 13.01.2020
15.01.2020 C Receive 16.01.2020
18.01.2020 C Receive 02.02.2020
20.01.2020 C Receive 17.01.2020
01.02.2020 C Send
Expected Result (also in attached file):
Date Number of "Items" with "receive" "Plan date" prior or equal to "Date" with latest "save date" prior or equal to "Date"
05.01.2020 3
06.01.2020 3
07.01.2020 3
08.01.2020 3
09.01.2020 0
10.01.2020 0
11.01.2020 1 < this is tricky
12.01.2020 2 < this is tricky
13.01.2020 3
14.01.2020 3
15.01.2020 2 < this is tricky
16.01.2020 3
17.01.2020 3
18.01.2020 0
19.01.2020 0
20.01.2020 3
21.01.2020 3
22.01.2020 3
and so on 3
When I have 3 columns (Date, Item, Bool) I have no problem, to find the latest save date for each item relative to current date and detect if the plan date of that latest saved line is on or prior to current date. But when I remove the Item column from the table, I struggle to change the DAX formula to "iterate" over all items.
My PowerBi Model has only two tables. The sample data table (Transaktionen Simple) and a calendar table (Dates) with consecutive dates.
The DAX to get the above mentioned Bool is this:
Code: Select all
PlanDateInPast =
Var CurrDate = Max(Dates[Date])
VAR maxSave = MAXX(FILTER('Transaktionen Simple';
[Save Date] <= CurrDate && [Save Date]);
[Save Date])
VAR Plan = MAXX(FILTER('Transaktionen Simple';
[Save Date] = maxSave && [Plan Date]);
[Plan Date])
var PastPlan = IF(Plan <= CurrDate && Plan; 1; 0)
Return
PastPlan