Is it possible to set dates used in a visual to min of a date?

[arpost]

I have a unique scenario and am hoping someone can help me with it. I am wanting to track values of data over time but annotate when certain percentages changed with what would be the equivalent of a dynamic "constant" line. Here's a mock verson of the data:

Values

Date	Category	Value
1/1/2014	New	$10,000
1/2/2014	Returning	$25,000

Change Event

Date	Percentage
1/1/2013	64.5%
6/1/2014	65.5%

I've "hacked" this in my report by using a Line and clustered column chart with the Values set to the Line portion and the percentage change events as Column values, which currently appear as follows:

This works well enough, but I am hitting a roadblock when the Date of the change event pre-dates the start of the data; in this example, the data may start in 2014, but the change event from 2013 is still showing:

Anyone have some clever thoughts on how I might go about making it so this visual only shows data that starts when the line values start?

[BA_Pete]

Hi @arpost ,

I'm probably oversimplifying this, but have you tried something like this?

_yourPercentageMeasure =
IF(
  ISBLANK([yourLineValueMeasure]),
  BLANK(),
  [yourPercentageValueMeasure]
)

Pete

[arpost]

@BA_Pete, thanks for posting. I just tried your suggestion but couldn't get it to work. These values are all just base fields, so no measures. When I attempted to convert to using measures with the ISBLANK logic, all change event columns disappeared because there was no overlap between the date of the % change and the date of values entered. I've got the visual configured as such:

[BA_Pete]

Ok, so I guess more like this:

_yourPercentageMeasure =
IF(
  ISBLANK( SUM(yourTable[Value 1]) + SUM(yourTable[Value 2]) ),
  BLANK(),
  SELECTEDVALUE(yourTable[Fee Change])
)

I'm not sure if SELECTEDVALUE will work in your scenario, so feel free to swap this for MAX or MIN or similar.

Pete

[arpost]

Thanks for that, @BA_Pete. Sadly, no joy. 
Measure:

Result:

I did a little auditing and confirmed that the values in question don't have entries for specific dates. Using a table, I selected a % Change Date, which filtered to $0 for the Values because there were no values entered on 6/20/19.

[BA_Pete · Accepted solution]

OK, then maybe something like this:

_yourPercentageMeasure = 
VAR __maxDate =
CALCULATE(
    MAX(changeEvent[Date]),
    ALL(changeEvent)
)
RETURN
CALCULATE(
    MAX(changeEvent[Percentage]),
    FILTER(
        changeEvent,
        changeEvent[Date] = __maxDate
    )
)

I'm assuming here that your changeEvent table is related to your calendar table from which the shared date axis comes.

I seem to get the required output (I had to change the dates to fit with my calendar)

Pete

[arpost]

That did it, @BA_Pete! I had to make some edits to make it start with the MIN date (and will probably need to make more for the max), but it is now performing as expected:

Here is a sanitized version of the code:

Percentage Change = 
VAR minDate =
CALCULATE(
MIN(Values[Date]),
ALL(Values)
)
RETURN
CALCULATE(
MAX('Change Event'[Percentage]),
FILTER(
'Change Event',
'Change Event'[Date] >= minDate
)
)

Thanks again; you're the best.