Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ACSchnitzers
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. 

 

DivComModStartDateMajorCodeMinorCodeCommittedCostBudgetedCost

XXX

Jan 1 202012210.1510
XXXJan 1 20201231012.17
XXXJune 4 202012210.3015
XXXJune 4 2020123102315
XXYFebruary 1 20221451034.54

 

Result:

DivComModDateCommittedCostDiffBudgetedCostDiff
XXXJan 1 20200%0%
XXXJun 4 202091.78%194.99%
XXYFebruary 1, 20220%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
v-jingzhang
Community Support
Community Support

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

vjingzhang_0-1674638481876.png

 

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

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

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.

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

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?

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. 

Hi @ACSchnitzers 

 

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

vjingzhang_0-1675417650708.png

 

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

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

 

Zero%Merge.png

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

 

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

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
Super User
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 ))

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors