DAX expert needed

[toralf]

Hi,

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.

The goal is to create a rolling count over time of all items that have their latest (save date <= current date) plan date for a specific action today or prior to that date (plan date <= current 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

[burque505]

Hi @toralf, might this DAX tips link be of some use?
Regards,
burque505

[toralf]

Thank you very much for this link. It might become handy in the future.
But it doesn’t help me with my current problem.

[haichen]

I worked with DAX and M in Power BI some time ago. Maybe I can help. Of course we can speak German.

[toralf]

Thanks, I‘ll post the Problem here On Monday in English. (We can have a German discussion afterwards via PM or in the German subforum if you prefer.)

[toralf]

I worked with DAX and M in Power BI some time ago. Maybe I can help. Of course we can speak German.

Dear haichen, I added the problem in the OP.

[haichen]

Hello toralf,
unfortunately I can't understand the logic.
Could you for once reconstruct for individual dates which of them are counted?

Count all items when plan date <= current date and save date <= current date:
On 5.1. there are A,B,C with plan 2.1, 3.1, 4.1 =>3
On 6.1 the same?
On 7.1 the same?
On 8.1 the same?
On 9.1 all items, plan date <= current date, save date <= should it not also be 3?
..
There must be a mistake in thinking.

[toralf]

On 9.1. all items are stored with new Plan Dates Which happen to be in the future (11th, 12th and 13th respectively) thus count zero
On 10.1 zero
On 11.1 Only A ( saved on 9.1)
On 12.1 A and B ( both happend to be saved on 9.1. But that Is just coincidence)
On 13.1. All items with new saved plan dates which happen to be identical to 9.1
On 14.1 all items
On 15.1 all items with newMy saved plan dates (14., 15. And 16.) thus 2
On 16.1 all items with plan dates saved on 15.1

[toralf]

I have found a nearly working solution. But not perfect.
the filter context while sumx the different items does not seem to be correct.
I'll also attach the pbix file if someone is interested to take a peak.

20200725 SimpleData_2 - Power BI Desktop (Januar 2020) - 000273.jpg (134.51 KiB) Viewed 5203 times