Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |