Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm looking to create the realized column shown in the table below. It works off the following pseudo-code:
If "Trade Type" = "New Trade" and there is no "Full Unwind" for this ID and "Expiry Date" >= Today(), then "N".
Otherwise, "Y".
ID | Trade type | Realised | Today | Expirey Date |
1 | New Trade | Y | 01/11/2019 | 01/10/2020 |
1 | Full unwind | Y | 01/11/2019 | 02/10/2020 |
2 | New Trade | N | 01/11/2019 | 05/09/2020 |
3 | New Trade | Y | 01/11/2019 | 15/10/2018 |
4 | New Trade | Y | 01/11/2019 | 05/10/2020 |
4 | Full unwind | Y | 01/11/2019 | 06/10/2020 |
5 | New Trade | N | 01/11/2019 | 07/07/2020 |
6 | New Trade | Y | 01/11/2019 | 18/11/2020 |
6 | Full unwind | Y | 01/11/2019 | 18/11/2020 |
7 | New Trade | N | 01/11/2019 | 10/10/2021 |
8 | New Trade | N | 01/11/2019 | 11/08/2020 |
9 | New Trade | N | 01/11/2019 | 17/05/2020 |
10 | New Trade | Y | 01/11/2019 | 13/10/2019 |
How would I code this in Dax?
Many thanks in advance
Solved! Go to Solution.
@Anonymous
Please create a calculated column as per below code
Column =
VAR tt = CALCULATE(CONCATENATEX(Test,Test[Trade type]," "),ALLEXCEPT(Test,Test[ID]))
RETURN IF(AND(tt="New Trade",Test[Expirey Date]>=Test[Today]),"N","Y")
@Anonymous
Please create a calculated column as per below code
Column =
VAR tt = CALCULATE(CONCATENATEX(Test,Test[Trade type]," "),ALLEXCEPT(Test,Test[ID]))
RETURN IF(AND(tt="New Trade",Test[Expirey Date]>=Test[Today]),"N","Y")
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |