Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |