Need a percentage difference based on earliest date in slicer.

[ACSchnitzers]

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.

[v-jingzhang]

Hi @ACSchnitzers 

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.

[ACSchnitzers]

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

[v-jingzhang]

Hi @ACSchnitzers 

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.

[ACSchnitzers]

@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?

[v-jingzhang]

Hi @ACSchnitzers 

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?

[ACSchnitzers]

Hi @v-jingzhang 

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. 

[ACSchnitzers]

@v-jingzhang -- Use this link for a sample file.

https://www.dropbox.com/s/m7adhigh240eeev/BudgetCostClean20230202.pbix?dl=0

[v-jingzhang]

Hi @ACSchnitzers 

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.

[ACSchnitzers]

@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. 

[ACSchnitzers]

@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)))

[ACSchnitzers]

@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.

[v-jingzhang]

Hi @ACSchnitzers 

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. 

[amitchandak]

@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 ))

[ACSchnitzers]

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?