cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Need a percentage difference based on earliest date in slicer.

I am trying to have a percentage change line graph from the earliest point on the graph through the following points. I.e. the first point should be 0%. This needs to be dynamic. I can only figure out a static percentage change.

I have a date table, and a sumx measure for the aggregate function by date and cost codes.

 DivComMod StartDate MajorCode MinorCode CommittedCost BudgetedCost XXX Jan 1 2020 122 10 .15 10 XXX Jan 1 2020 123 10 12 .17 XXX June 4 2020 122 10 .30 15 XXX June 4 2020 123 10 23 15 XXY February 1 2022 145 10 34 .54

Result:

 DivComMod Date CommittedCostDiff BudgetedCostDiff XXX Jan 1 2020 0% 0% XXX Jun 4 2020 91.78% 194.99% XXY February 1, 2022 0% 0%

I've had no luck with the following measures showing me the graphic needed:

EarliestBudgetCost =
Calculate(
Sum(MajMinorCostPerSF[BudgetedAvgCostSF]),
Filter(ALLSELECTED(MajMinorCostPerSF),MajMinorCostPerSF[StartDate]=MIN(MajMinorCostPerSF[StartDate])))

EarliestCommittedCost =
Calculate(
Sum(MajMinorCostPerSF[CommittedAvgCostSF]),
Filter(ALLSELECTED(MajMinorCostPerSF),MajMinorCostPerSF[StartDate]=MIN(MajMinorCostPerSF[StartDate])))

I have also attempted to make the Earliest Date selected by the user.
%ChangeCommittedCost =
var CurStart = MajMinorCostPerSF[CommittedCost]
var SelectedDate = SelectedValue(ReferenceDate[StartDate])
var RefStart = Calculate(MajMinorCostPerSF[CommittedCost],FILTER(MajMinorCostPerSF,MajMinorCostPerSF[StartDate] = SelectedDate))
RETURN
DIVIDE(CurStart - RefStart,RefStart)

Any help is greatly apprecaited.
14 REPLIES 14
Community Support

You can try these measures

``````BudgetCostDiff =
var _earliestDate = CALCULATE(MIN('MajMinorCostPerSF'[StartDate]),ALLSELECTED('MajMinorCostPerSF'[StartDate]))
var _earliestBudgetCost = CALCULATE(SUM(MajMinorCostPerSF[BudgetedCost]),ALLSELECTED('MajMinorCostPerSF'[StartDate]),'MajMinorCostPerSF'[StartDate]=_earliestDate)
return
DIVIDE(SUM(MajMinorCostPerSF[BudgetedCost]),_earliestBudgetCost)-1``````
``````CommittedCostDiff =
var _earliestDate = CALCULATE(MIN('MajMinorCostPerSF'[StartDate]),ALLSELECTED('MajMinorCostPerSF'[StartDate]))
var _earliestCommittedCost = CALCULATE(SUM(MajMinorCostPerSF[CommittedCost]),ALLSELECTED('MajMinorCostPerSF'[StartDate]),'MajMinorCostPerSF'[StartDate]=_earliestDate)
return
DIVIDE(SUM(MajMinorCostPerSF[CommittedCost]),_earliestCommittedCost)-1``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Frequent Visitor

Thanks . This is what I am seeking. How would I adjust this to allow for a selected date in a slicer and use the next smallest date as zero?

E.g. Select June 7th and June 4th's numbers are the denominator.

I attempted the below, but it looks like each line is being divided by itself giving all zeros. In short, I want the selected number to become the new earliest date and if this date does not exist for the set, the formula uses the next smallest date for zero.

``````BudgetCostDiff =
var _selecteddate = SelectedValue(MajMinorCostPerSF[StartDate])
var _earliestDate = CALCULATE(MAX('MajMinorCostPerSF'[StartDate]),Filter(MajMinorCostPerSF,MajMinorCostPerSF[StartDate]<= _selecteddate),ALLSELECTED('MajMinorCostPerSF'[StartDate]))
var _earliestBudgetCost = CALCULATE(SUM(MajMinorCostPerSF[BudgetedAvgCostSF]),ALLSELECTED('MajMinorCostPerSF'[StartDate]),'MajMinorCostPerSF'[StartDate]=_earliestDate)
return
DIVIDE(SUM(MajMinorCostPerSF[BudgetedAvgCostSF]),_earliestBudgetCost)-1``````

@v-jingzhang

Community Support

You need to add a disconnected table to have dates for the slicer. Then try measures

``````CommittedCostDiff =
var _selectedDate = SELECTEDVALUE('Table'[Date])
var _earliestDate = CALCULATE(MAX('MajMinorCostPerSF'[StartDate]),ALLSELECTED(MajMinorCostPerSF[StartDate]),'MajMinorCostPerSF'[StartDate]<=_selectedDate)
var _earliestCommittedCost = CALCULATE(SUM(MajMinorCostPerSF[CommittedCost]),ALLSELECTED('MajMinorCostPerSF'[StartDate]),'MajMinorCostPerSF'[StartDate]=_earliestDate)
return
IF(SELECTEDVALUE(MajMinorCostPerSF[StartDate])<_earliestDate,BLANK(),DIVIDE(SUM(MajMinorCostPerSF[CommittedCost])-_earliestCommittedCost,_earliestCommittedCost))
``````
``````BudgetCostDiff =
var _selectedDate = SELECTEDVALUE('Table'[Date])
var _earliestDate = CALCULATE(MAX('MajMinorCostPerSF'[StartDate]),ALLSELECTED(MajMinorCostPerSF[StartDate]),'MajMinorCostPerSF'[StartDate]<=_selectedDate)
var _earliestBudgetCost = CALCULATE(SUM(MajMinorCostPerSF[BudgetedCost]),ALLSELECTED('MajMinorCostPerSF'[StartDate]),'MajMinorCostPerSF'[StartDate]=_earliestDate)
return
IF(SELECTEDVALUE(MajMinorCostPerSF[StartDate])<_earliestDate,BLANK(),DIVIDE(SUM(MajMinorCostPerSF[BudgetedCost])-_earliestBudgetCost,_earliestBudgetCost))
``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Frequent Visitor

@v-jingzhang Sorry for the delay- birth of child pulled me away.

Thank you for this. I have made an adjustment to help centralize some of the dates that are too far off from each other by averaging weekly data versus daily.

With this being said, is it possible to have a measure or table that would show all points as if they started on the same date as the selected?

For example:

Selecting November 1 2022 on the slicer uses October 28th of DivComMod A as point 1 (zero) and October 15th of DivComMod B for point 1 (zero), the line graph is showing 2 separate points for a percentage change comparison. Can a non-existist point be referenced as the start to align comparisons on the same timeline? E.g. DivComMod A's and B's point 1s (zero) are both November 1 and the value behind the first point is A's October 28th and B's October 15th values.

In other words, could the Max point less than the selected date remain at zero until reaching the selected date on the X axis or the date within the data be dynamically adjusted to match the selected date?

Community Support

Do you add DivComMod to Legend on a line chart? Can you share some sample data which has more dates for me to have a test?

Frequent Visitor

See attached. I have cleaned up my data for your review. See page "PercentDiffBySelectedDate".

The selected date is November 11, 2022, however, I have zero points going back to August 2nd in some cases as this is the max date less than the selected. As you can see by my dotted selected date, the measures are too far spread to compare against a zero starting point. I'd like the selected date to be the zero point for all lines.

Maybe a calculation table where a column is dynamic based on the selected slicer date?
"If Max Date < Selected Date, then use Selected Date, else use [StartDate]"

I need to sit down at my computer to upload the attachment. Standby.

Frequent Visitor
Community Support

Thank you for the sample file. You can try replacing _earliestDate with _selectedDate in the returned statement. Just like below.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Frequent Visitor

@v-jingzhang -- Thank you for your response.

This is closer, but the same issue exists. This solution changes the x-axis start and the DivComMod first visible point as opposed to all DivComMod first visible point being the selected date and at zero. See example for what I am trying to accomplish. I assume I will need to create a new column to drive the first point of each line to merge at zero % for the selected date.

Frequent Visitor

@v-jingzhang -- I am getting closer with this measure, but if I do not select a date that exists for a DivComMod, my graph shows a 0% across all points for that DivComMod. Can I use treatas or another method to have all DivComMod cross the Y axis at zero at the same point on the X axis? Do I need to add blank dates to my data table?

``````MissingValue =
var _SelectedDate = selectedvalue(DisconnectedDate[Date])
var _DivComMod = Max(MajMinCostPerSf[DivComMod])
var _earliestDate = CALCULATE(MAX(MajMinCostPerSF[StartDate]),ALLSELECTED(MajMinCostPerSF[StartDate]),MajMinCostPerSF[StartDate]<=_selectedDate ,MajMinCostPerSF[DivComMod] = _DivComMod)
var _earliestBudgetedCost = CALCULATE(SUM(MajMinCostPerSF[Budget\$PerSF]),ALLSELECTED(MajMinCostPerSF[StartDate]),MajMinCostPerSF[StartDate]=_earliestDate, MajMinCostPerSF[DivComMod] = _DivComMod)
var _SelectedBudgetedCost = CALCULATE(SUM(MajMinCostPerSF[Budget\$PerSF]),ALLSELECTED(MajMinCostPerSF[StartDate]),MajMinCostPerSF[StartDate]=_SelectedDate, MajMinCostPerSF[DivComMod] = _DivComMod)

Return
IF(SelectedValue(MajMinCostPerSF[StartDate]) <_SelectedDate,BLANK(),IF(ISBLANK(_SelectedBudgetedCost),DIVIDE(_earliestBudgetedCost-_earliestBudgetedCost,_earliestbudgetedCost),DIVIDE(SUM(MajMinCostPerSF[Budget\$PerSF])-_earliestBudgetedCost,_earliestbudgetedCost)))``````

Frequent Visitor

@v-jingzhang -- As I was toying with this issue over the weekend, I duplicated the data table and added in the missing dates between the max and min dates for each DivComMod, then converted the nulls into zeros. My lines are now all straight 0% lines using the formula above. I believe this is because it's calculating all zeros against the max value less than the selected value. Any help you can provide is greatly appreciated.

Community Support

Sorry I haven't made it. I will let you know once I have a solution. I think adding a new column will not help as a calculated column cannot be influenced dynamically by a slicer.

Super User

@ACSchnitzers , Tried like

EarliestBudgetCost =
var _min = MINX(ALLSELECTED(MajMinorCostPerSF), MajMinorCostPerSF[StartDate])
return
Calculate(
Sum(MajMinorCostPerSF[BudgetedAvgCostSF]),
Filter(MajMinorCostPerSF,MajMinorCostPerSF[StartDate]=_min))

EarliestCommittedCost =
var _min = MINX(ALLSELECTED(MajMinorCostPerSF), MajMinorCostPerSF[StartDate])
return
Calculate(
Sum(MajMinorCostPerSF[CommittedAvgCostSF]),
Filter((MajMinorCostPerSF),MajMinorCostPerSF[StartDate]=_min ))

Frequent Visitor

Thanks @amitchandak -- This provides the earliest startdate across the entire table. I need the earliest startdate for each DivComMod. This will allow for me to calculate percentage change between the earliest cost and latter for each DivComMod. Is there a grouping function?