Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have the following code which doesn't work:
EDATE (
CALCULATE (
MIN ( Option[Option Start Date] ),
Option[Option Category] = "Termination Option",
Option[Option Start Date] > _asofdate
),
CALCULATE (
SUM ( Option[Notice Period] ),
MINX ( Option, Option[Option Start Date] ),
Option[Option Category] = "Termination Option",
Option[Option Start Date] > _asofdate
)
)
I am trying to find the lowest date found in the [Option Start Date] column after filtering out rows that don't have an [Option Category] as 'Termination Option' and where [Option Start Date] is greater than variable _asofdate - which is assigned to a date picker in my report.
My trouble begins with the next statement where the EDATE requires a number to advance the date in months.
It throws an error because I am not declaring a true/false expression with MINX.
What I want it to do is essentially use the same row that was filtered in the first statement that got the start date, but to return whatever is in the [Notice Period] column of that row.
I was trying to place the MIN/MINX so that the results returned would mirror the filtering applied from the first statement.
What is the correct and most efficient way of achieving this?
Thank you.
@julesdude ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
You can refer this approach, you need Min instead of MAX
Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
Here's a sample dataset to help.
The table is called Option and only this table is referenced for the DAX.
Asset Reference | Lease Reference | Unit Reference | Option Category | Option Start Date | Notice Period | Option Effective Date | Score |
aaa | 1 | 1a1a | Termination Option | 31-Jul-22 | 6 | 3 | |
bbb | 2 | 2a2a | Termination Option | 01-Apr-23 | 6 | 30-Sep-23 | 1 |
ccc | 3 | 3a3a | Termination Option | 17-Jan-23 | 6 | 17-Jul-23 | 1 |
ddd | 4 | 8026 | Termination Option | 31-Aug-22 | 12 | 31-Aug-28 | 1 |
eee | 5 | a4a4 | Renew Extend | 07-Mar-22 | 6 | 08-Sep-22 | 1 |
eee | 5 | a5a5 | Renew Extend | 07-Mar-22 | 6 | 08-Sep-22 | 1 |
eee | 5 | a6a6 | Renew Extend | 07-Mar-22 | 6 | 08-Sep-22 | 1 |
fff | 6 | 7777 | Termination Option | 31-Aug-23 | 6 | 31-Aug-23 | 1 |
ggg | 7 | 8888 | Termination Option | 24-Jun-23 | 6 | 30-Jun-23 | 1 |
hhh | 8 | 9999 | Renew Extend | 30-Nov-23 | 13 | 01-Jan-25 | 1 |
hhh | 9 | 1010 | Renew Extend | 31-Mar-22 | 14 | 01-Jun-23 | 1 |
hhh | 10 | 222 | Renew Extend | 31-Mar-22 | 14 | 01-Jun-23 | 1 |
hhh | 11 | 1111 | Renew Extend | 30-Jun-23 | 12 | 01-Jul-24 | 1 |
hhh | 12 | 2a2a | Renew Extend | 28-Feb-22 | 9 | 01-Dec-22 | 1 |
iii | 13 | 1a | Renew Extend | 01-Apr-22 | 13 | 03-May-23 | 1 |
iii | 13 | 1a | Renew Extend | 01-Apr-22 | 13 | 03-May-23 | 1 |
hhh | 14 | 2a2a | Renew Extend | 28-Feb-22 | 9 | 01-Dec-22 | 1 |
hhh | 15 | 222 | Renew Extend | 31-Mar-22 | 14 | 01-Jun-23 | 1 |
hhh | 16 | 4444 | Renew Extend | 31-Mar-22 | 14 | 01-Jun-23 | 1 |
hhh | 17 | 5555 | Renew Extend | 30-Apr-22 | 9 | 01-Feb-23 | 1 |
jjj | 18 | 6666 | Renew Extend | 31-May-23 | 12 | 01-Jun-24 | 1 |
jjj | 29 | 7777 | Renew Extend | 30-May-22 | 12 | 01-Jun-23 | 1 |
kkk | 20 | 8888 | Renew Extend | 31-May-22 | 13 | 01-Jul-23 | 1 |
My whole measure, that doesn't work:
Next Option Effective Date =
VAR _asofdate = [As Of Date]
VAR _score =
SUM ( Option[Score] )
VAR _date =
SWITCH (
TRUE (),
_score = 3,
EDATE (
CALCULATE (
MIN ( Option[Option Start Date] ),
Option[Option Category] = "Termination Option",
Option[Option Start Date] > _asofdate
),
CALCULATE (
SUM ( Option[Notice Period] ),
MINX ( Option, Option[Option Start Date] ),
Option[Option Category] = "Termination Option",
Option[Option Start Date] > _asofdate
)
),
_score = 4,
EDATE (
_asofdate,
CALCULATE (
SUM ( Option[Notice Period] ),
MINX ( Option, Option[Option Start Date] ),
Option[Option Category] = "Termination Option",
Option[Option Start Date] > _asofdate
)
),
BLANK ()
)
RETURN
_date
I want to achieve the following:
1. for score = 3 - to get the second argument of EDATE to advance the date in months. It needs to get this from the [Notice Period] column by filtering only rows where [Option Start Date] is > _asofdate AND where [Option Category] = 'Termination Option'. Then get the [Notice Period] from the row where the [Option Start Date] is closest to _asofdate
2. for score = 4 - to get the second argument of EDATE to advance the date in months I need to do exactly the same as above.
Thank you.
hi @julesdude
you have provided a nice dataset. what do you expect out of this? (try to explain in plain language without referring to what you tried so far)
Hi @FreemanZ
Yes I managed to figure out a way from the links @amitchandak sent, in which the solutions I could adapt a little for this problem I was having.
I dropped out the two lines that were causing the issue:
MINX ( Option, Option[Option Start Date] )
Of course, you cannot use such a statement as a true or false expression.
I instead replaced it with:
_score = 3,
EDATE (
CALCULATE (
MIN ( Option[Option Start Date] ),
Option[Option Category] = "Termination Option",
Option[Option Start Date] > _asofdate
),
CALCULATE (
SUM ( Option[Notice Period] ),
Option[Option Start Date] = MIN ( Option[Option Start Date] ),
Option[Option Category] = "Termination Option",
Option[Option Start Date] > _asofdate
)
),
_score = 4,
EDATE (
_asofdate,
CALCULATE (
SUM ( Option[Notice Period] ),
Option[Option Start Date] = MIN ( Option[Option Start Date] ),
Option[Option Category] = "Termination Option",
Option[Option Start Date] > _asofdate
)
),
Still need to test it more but that seems to provide me with the filtered row option i need.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |