Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Looking for a way in Power BI to calculate the difference between values in the same column. Need the day over day change to the remaining duration. I have a formula that works but an issue has come up when durations are actually increased. I would prefer to not show negative change and the change be based off the original duration only. Please see existing formula and example data below.
Any help is greatly appreciated!
Solved! Go to Solution.
Hi @Daxtothemax ,
Please check the formula.
_flag =
var a = CALCULATE(SUM(DS[Remaining Duration]),FILTER(DS,DS[date]=EARLIER(DS[date])-1&&DS[Task]=EARLIER(DS[Task])))
return
if(a=blank()||a>=DS[Remaining Duration],1,0)
change =
var _date = CALCULATE(MAX(DS[date]),FILTER(DS,DS[date]<EARLIER(DS[date])&&DS[Task]=EARLIER(DS[Task])&&DS[_flag]=1))
var _value = CALCULATE(SUM(DS[Remaining Duration]),FILTER(DS,DS[Task]=EARLIER(DS[Task])&&DS[date]=_date))
return
IF(_value <DS[Remaining Duration],0,_value-DS[Remaining Duration])
Best Regards,
Jay
Hi @Daxtothemax ,
Please check the formula.
_flag =
var a = CALCULATE(SUM(DS[Remaining Duration]),FILTER(DS,DS[date]=EARLIER(DS[date])-1&&DS[Task]=EARLIER(DS[Task])))
return
if(a=blank()||a>=DS[Remaining Duration],1,0)
change =
var _date = CALCULATE(MAX(DS[date]),FILTER(DS,DS[date]<EARLIER(DS[date])&&DS[Task]=EARLIER(DS[Task])&&DS[_flag]=1))
var _value = CALCULATE(SUM(DS[Remaining Duration]),FILTER(DS,DS[Task]=EARLIER(DS[Task])&&DS[date]=_date))
return
IF(_value <DS[Remaining Duration],0,_value-DS[Remaining Duration])
Best Regards,
Jay
Hi @Daxtothemax
You may try
Diff =
VAR TaskTable =
CALCULATETABLE ( DS, ALLEXCEPT ( DS, DS[Task] ) )
VAR FirstDate =
MINX ( TaskTable, DS[Date] )
VAR FirstDuration =
MAXX ( FILTER ( TaskTable, DS[Date] = FirstDate ), DS[Hrs] )
VAR Result = FirstDuration - DS[Hrs]
RETURN
IF ( Result < 0, 0, Result )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |