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, Can anyone help me with the following DAX measure.
I have a table below like this.
Operation Date | Operation Type | CashFlow |
12/23/2009 | Call | -15107386.12 |
12/23/2009 | Return of Excess | 302958.48 |
12/31/2009 | Valuation | 12232880 |
10/22/2013 | Call | -2653076.64 |
10/22/2013 | Distribution | 1359517.24 |
9/30/2017 | Valuation | 84501842 |
10/31/2017 | Cash-Adjusted Valuation | 84501842 |
11/30/2017 | Cash-Adjusted Valuation | 84501842 |
12/31/2017 | Valuation | 83147982 |
1/24/2018 | Distribution | 998946.54 |
1/31/2018 | Cash-Adjusted Valuation | 82149035.46 |
2/23/2018 | Distribution | 2042295.89 |
2/28/2018 | Cash-Adjusted Valuation | 80106739.57 |
3/31/2018 | Valuation | 78814652 |
I am using a Operation Date Slicer with before Operand, If I select Operation Data as 3/31/2018, Then I should see only the Max Valuation or Cash Adjusted Valuation and all other operation types. The expected output is below:
Operation Date | Operation Type | CashFlow |
12/23/2009 | Call | -15107386.12 |
12/23/2009 | Return of Excess | 302958.48 |
10/22/2013 | Call | -2653076.64 |
10/22/2013 | Distribution | 1359517.24 |
1/24/2018 | Distribution | 998946.54 |
2/23/2018 | Distribution | 2042295.89 |
3/31/2018 | Valuation | 78814652 |
If I select 2/28/2018 Then the output should be
Operation Date | Operation Type | CashFlow |
12/23/2009 | Call | -15107386.12 |
12/23/2009 | Return of Excess | 302958.48 |
10/22/2013 | Call | -2653076.64 |
10/22/2013 | Distribution | 1359517.24 |
1/24/2018 | Distribution | 998946.54 |
2/23/2018 | Distribution | 2042295.89 |
2/28/2018 | Cash-Adjusted Valuation | 80106739.57 |
I used the below expression to filter out all Valuation and Cash Adjusted Valuation. I should only show the TOP Valuation or Cash Adjusted Valuation based on the date I select.
VIRRCashFlow =
CALCULATE(Sum('Funds_PI - Operations'[Cashflow]),
FILTER('Funds_PI - Operations', 'Funds_PI - Operations'[Operation Date] <= MAX([Operation Date]) && 'Funds_PI - Operations'[Operation Type] <> "Valuation" && 'Funds_PI - Operations'[Operation Type] <> "Cash-Adjusted Valuation"))
Please help!
Thanks,
Nikhil
Solved! Go to Solution.
Hi @nikhil425,
For your case, please create the measure below and then put it in visual level filter.
Measure 3 = IF ( NOT ( SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Type] ) IN { "Valuation", "Cash-Adjusted Valuation" } ) && SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Date] ) <= MAX ( [Operation Date] ), "Y", IF ( SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Date] ) = MAXX ( ALLSELECTED ( 'Funds_PI - Operations'[Operation Date] ), [Operation Date] ), "Y" ) )
Then you could get the output below.
More details, you could refer to the attachment.
Best Regards,
Cherry
Hi @nikhil425,
For your case, please create the measure below and then put it in visual level filter.
Measure 3 = IF ( NOT ( SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Type] ) IN { "Valuation", "Cash-Adjusted Valuation" } ) && SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Date] ) <= MAX ( [Operation Date] ), "Y", IF ( SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Date] ) = MAXX ( ALLSELECTED ( 'Funds_PI - Operations'[Operation Date] ), [Operation Date] ), "Y" ) )
Then you could get the output below.
More details, you could refer to the attachment.
Best Regards,
Cherry
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |