Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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.
As you can see, that record is found in the next Monday, so it cannot be marked as Outflow.
Thank you,
Melisa
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |