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,
Please help me in solving the below issue using DAX
I have a quarterly data with list of items as below.
Date | Items | Quarter
01/01/2020 | A | Current Quarter
01/01/2020 | B | Current Quarter
01/01/2020 | C | Current Quarter
01/01/2020 | D | Current Quarter
01/12/2019 | A | Previous Quarter
01/12/2019 | D | Previous Quarter
01/12/2019 | F | Previous Quarter
Here I need to fetch the added the items and removed items in the current quarter comparing to previous quarter
Added Items
B
C
Removed Items
F
Please anyone could tell me how to fetch the added and remvoed items using DAX...
Thanks in advance.
Hi! interesting situation. If you have a good refresh of current and previous quarter flag column you can build columns like this for new:
IsNew =
IF (
AND(
newfromquarter[Quarter] = "Current Quarter";
NOT(
newfromquarter[Items] IN
SELECTCOLUMNS(
FILTER(
VALUES(newfromquarter);
newfromquarter[Quarter] = "Previous Quarter"
); "Item"; newfromquarter[Items]
)
)
)
; "Yes"
; "No"
)
And something like this for the removed:
IsRemoved =
IF (
AND(
newfromquarter[Quarter] = "Previous Quarter";
NOT(
newfromquarter[Items] IN
SELECTCOLUMNS(
FILTER(
VALUES(newfromquarter);
newfromquarter[Quarter] = "Current Quarter"
); "Item"; newfromquarter[Items]
)
)
)
; "Yes"
; "No"
)
Then you can just filter in your report pages by this new column to understand the added and removed items.
Hope this helps,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |