DAX expert needed

Discuss other programming languages besides AutoHotkey
toralf
Posts: 792
Joined: 27 Apr 2014, 21:08
Location: Germany

DAX expert needed

16 Jul 2020, 15:51

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
Attachments
SimpleData.xlsx
(11.29 KiB) Downloaded 17 times
Last edited by toralf on 20 Jul 2020, 02:02, edited 4 times in total.
ciao
toralf
burque505
Posts: 1398
Joined: 22 Jan 2017, 19:37

Re: Dax expert needed

18 Jul 2020, 12:08

Hi @toralf, might this DAX tips link be of some use?
Regards,
burque505
toralf
Posts: 792
Joined: 27 Apr 2014, 21:08
Location: Germany

Re: Dax expert needed

19 Jul 2020, 05:29

Thank you very much for this link. It might become handy in the future.
But it doesn’t help me with my current problem.
ciao
toralf
User avatar
haichen
Posts: 544
Joined: 09 Feb 2014, 08:24

Re: DAX expert needed

19 Jul 2020, 06:48

I worked with DAX and M in Power BI some time ago. Maybe I can help. Of course we can speak German.
toralf
Posts: 792
Joined: 27 Apr 2014, 21:08
Location: Germany

Re: DAX expert needed

19 Jul 2020, 14:04

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.)
ciao
toralf
toralf
Posts: 792
Joined: 27 Apr 2014, 21:08
Location: Germany

Re: DAX expert needed

20 Jul 2020, 01:45

haichen wrote:
19 Jul 2020, 06:48
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.
ciao
toralf
User avatar
haichen
Posts: 544
Joined: 09 Feb 2014, 08:24

Re: DAX expert needed

20 Jul 2020, 10:00

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
Posts: 792
Joined: 27 Apr 2014, 21:08
Location: Germany

Re: DAX expert needed

20 Jul 2020, 10:25

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
ciao
toralf
toralf
Posts: 792
Joined: 27 Apr 2014, 21:08
Location: Germany

Re: DAX expert needed

25 Jul 2020, 05:41

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
20200725 SimpleData_2 - Power BI Desktop (Januar 2020) - 000273.jpg (134.51 KiB) Viewed 1645 times
Attachments
SimpleData_2.zip
(111.81 KiB) Downloaded 32 times
ciao
toralf

Return to “Other Programming Languages”

Who is online

Users browsing this forum: No registered users and 0 guests