Replicate Excel logic in DAX

[Anonymous]

Hi,

I would like to replicate in Power BI, preferabily using DAX, the below excel logic:

1) Combining all the rows of a week (Monday-Sunday). The process in excel currently is the following: the user is adding to the Monday's data the new records found on Tuesday, after this is comparing Monday+new Tuesday's data  with the Wednesdey's records and is adding to the Monday+new Tuesday's data the Wednesday's new records   and so one until Sunday (included).

This combined data is then compared with the next  Monday data and what is not found on Monday's records is marked as Outflow. This process is being repetead for all the following weeks and what I want to retrieve is the number of the records marked as Outflows during a certain week, by creating a first column like the one in the sample - Result - in a new DAX column.

Thank you,

Melissa

record	Week	WeekDay	Date	Result - in a new DAX column
1245542	1	Sunday	03/15/2020	Outflow - is not appearing in the following Monday
1224544	2	Monday	03/16/2020
1224544	2	Tuesday	03/17/2020
1233443	2	Tuesday	03/17/2020	Outflow - is not appearing in the following Monday
1224544	2	Thurday	03/19/2020
1224547	2	Sunday	03/22/2020	Outflow - is not appearing in the following Monday
1224544	3	Monday	03/23/2020
1224544	3	Wednesday	03/25/2020	Outflow - is not appearing in the following Monday
1334555	4	Monday	03/30/2020

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Column = 
VAR x = 
CALCULATE(
    MAX(Sheet1[record]),
    FILTER(
        Sheet1,
        Sheet1[Week] = EARLIER(Sheet1[Week]) + 1  && Sheet1[WeekDay] = "Monday"
    )
)
RETURN
IF(
    Sheet1[record] <> x && Sheet1[WeekDay] <> "Monday",
    "Outflow", BLANK()
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

I am not following this, I do not understand why some rows are marked as Outflow and others are not, like one Tuesday is and one is not and also the Thursday.

[Anonymous]

@Greg_Deckler 

Hi Greg,

Any idea how can I replicate this logic in DAX?

Many thanks,

Melisa

[Anonymous]

Hi @Greg_Deckler 

As you can see, that record is found in the next Monday, so it cannot be marked as Outflow.

Thank you,

Melisa