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 ActionCode AAA. Fine, that's easy. Just select ActionCode = AAA into the Power BI filter.
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!
Edit: added sample data and expected output
| AccountID | Date | ActionCode | ActionQty |
|---|---|---|---|
| AC1 | 06-Apr-20 | AAA | 1 |
| AC1 | 09-Apr-20 | AAB | 1 |
| AC1 | 07-Apr-20 | BBB | 1 |
| AC2 | 16-Apr-20 | AAA | 1 |
| AC2 | 20-Apr-20 | AAB | 1 |
So when Filtering for AccountID AC1 and April 2020, this new measure would return 0 (because the number of working days between the AAA and AAB ActionCodes for this AccountID is greater than 2)
When filtering for AccountID AC2 and Aptil 2020, the new measure should return 1 (because the number of working days between 16 April and 20 April is less than 2)
