Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
Solved! Go to 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
Proud to be a Datanaut!
Hi @arpost ,
I'm probably oversimplifying this, but have you tried something like this?
_yourPercentageMeasure =
IF(
ISBLANK([yourLineValueMeasure]),
BLANK(),
[yourPercentageValueMeasure]
)
Pete
Proud to be a Datanaut!
@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:
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
Proud to be a Datanaut!
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.
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
Proud to be a Datanaut!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
79 | |
64 | |
52 | |
48 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |