Help with the complex DAX measure

[Anonymous]

I am struggling with a DAX measure. Here is the fact and 2 dimension tables I have:

factAction(dimAccountID, dimActionID, Date, ActionQty)
dimAction(dimActionID, ActionCode, ActionDescription)
dimDate(Date, WorkingDay)

The relationships are:

dimDate.Date -> factAction.Date
dimAction.dimActionID->factAction.dimActionID

And I have a DAX measure for ActionQty = SUM('factAction'[ActionQty])

We want to report on the number of actions of ActionCo

de AAA. Fine, that's easy. Just select ActionCode = AAA into the Power BI filter.

But now we want to also report, for those Accounts that had ActionCode AAA, the sum of ActionQty for ActionCode AAB where the ActionDate is within 2 working days of the AAA Action Date.

Any suggestions would be welcome!

Test data

AccountID	Date	ActionCode	ActionQty
AC1	6 Apr 2020	AAA	1
AC1	9 Apr 2020	AAB	1
AC1	7 Apr 2020	BBB	1
AC2	16 Apr 2020	AAA	1
AC2	20 Apr 2020	AAB	1

Expected results

Filter AccountID	Filter Date (mmm YYYY)	New measure expected result	Reason
AC1	Apr 2020	0	Working days between AAA and AAB is > 2
AC2	Apr 2020	1	Working days between AAA and AAB is <= 2

[Anonymous]

Please check out the solution carefully.

Best

D

[Anonymous]

Hi. Please post some test data so that we can use it to create a solution. Best would be to have a PBI file but if you can't, then data in textual form will do. No pictures please. You can create tables here using the message's editing tools.

By the way, you've forgotten about the dimAccount table.

Best
D

[Anonymous]

Test data and expected results added to the original post.

No test data is needed for dimAccount, as nothing from that table is needed - simply the dimAccountID from factAction.