Forum Discussion
Microsoft EmployeeHow to write measure to get incremental value for missing value for particular date?
Hi Experts,
Suppose I have below table in which I have some missing value on few days.
Scenerio 1
Day Value
day1 3
day2
day3
day4
day5
day6
day7 8
As we see above we have missing value from day 2 to day 6 .
I need to fill this empty space with some value based on my condition. So Condition be like
Condition 1:(Difference of max-min value/countofblankvalues+1)
(Ex: from scenerio 1 table max value is 8 and min value is 3 and countofblanks is: 5 then 8-3/5+1=0.83 )
Condition 2: Condition 1 output value should be added to all blank values (firstnonblank value) )
(Ex: condition 1 output is : 0.83 + day 1 value)
day1---3
day2---3+0.83=3.83 (Day 1 +condition 1 value : 0.83)
day3---3.83+0.83=4.66 (Day 2+condition 1 value : 0.83)
day4---3,83+0.83 =4.66 (Day 3 +condition 1 value : 0.83)
day5---5.5+0.83=6.3 (Day 4 +condition 1 value : 0.83)
day6---6.3+0.83=7.13 (Day 5 +condition 1 value : 0.83)
day7---8
How to write measure based on above condition in Power BI?
Thanks,
Annu
Anonymous
Please try this measure:Missing Fill = VAR _CURVALUE = [Metric Total] VAR _CURRDAY = SELECTEDVALUE ( DateTable[Date] ) VAR _PREVDAY = LASTNONBLANK ( FILTER ( ALLSELECTED ( DateTable[Date] ), DateTable[Date] < _CURRDAY ), [Metric Total] ) VAR _NEXTDAY = FIRSTNONBLANK ( FILTER ( ALLSELECTED ( DateTable[Date] ), DateTable[Date] > _CURRDAY ), [Metric Total] ) VAR _PREVAL = CALCULATE ( [Metric Total], DateTable[Date] = _PREVDAY, ALL ( DateTable ) ) VAR _NEXTVAL = CALCULATE ( [Metric Total], DateTable[Date] = _NEXTDAY, ALL ( DateTable ) ) VAR _BLANKS = DATEDIFF ( _PREVDAY, _NEXTDAY, DAY ) VAR _DIFF = DIVIDE ( _NEXTVAL - _PREVAL, _BLANKS ) VAR _BLANKINC = COUNTROWS ( FILTER ( ALLSELECTED ( DateTable[Date] ), DateTable[Date] < _CURRDAY && DateTable[Date] >= _PREVDAY ) ) VAR _INCREMENT = CALCULATE ( [Metric Total], DateTable[Date] = _PREVDAY ) + ( _DIFF * _BLANKINC ) RETURN IF ( ISBLANK ( _CURVALUE ), IF ( ISBLANK ( _PREVAL ), _NEXTVAL, _INCREMENT ), _CURVALUE )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
9 Replies
- Fowmy
Super User
Annu_choubey
I replaced the DAY in your table with actual dates, hope that will be the case in reality.
Here is the Measure:Missing Fill = VAR _CURVALUE = SELECTEDVALUE(Table1[Value]) VAR _CURRDAY = SELECTEDVALUE(Table1[Day]) VAR _PREVDAY = MAXX( FILTER(ALL(Table1),Table1[Day] < _CURRDAY && Table1[Value] <> BLANK()), Table1[Day]) VAR _NEXTDAY =MINX( FILTER(ALL(Table1),Table1[Day] > _CURRDAY && Table1[Value] <> BLANK()), Table1[Day]) VAR _PREVAL = CALCULATE( SUM(Table1[Value]), Table1[Day] = _PREVDAY,ALL(Table1)) VAR _NEXTVAL = CALCULATE( SUM(Table1[Value]), Table1[Day] = _NEXTDAY,ALL(Table1)) VAR _BLANKS = DATEDIFF(_PREVDAY,_NEXTDAY,DAY) VAR _DIFF = DIVIDE(_NEXTVAL - _PREVAL, _BLANKS ) VAR _BLANKINC = COUNTROWS( FILTER(ALL(Table1),Table1[Day] < _CURRDAY && Table1[Day] >= _PREVDAY)) VAR _INCREMENT = MAXX( FILTER(ALL(Table1),Table1[Day] < _CURRDAY && Table1[Value] <> BLANK()), Table1[Value]) + _DIFF * _BLANKINC RETURN IF( ISBLANK(_CURVALUE), _INCREMENT, _CURVALUE )
Expected Output:________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Hi Fowmy,
Thanks for the quick reply. I'm trying to implement the same thing but it is not working in my pbix . Attached is the pbix file . Can you help me out on these ?
Pbix File : https://1drv.ms/u/s!Au-aOkl1BoHugijnugHCN0fzY8jq?e=DgtBAa
Thanks In Advance.
- Fowmy
Anonymous Annu_choubey
I modified the measure based on the model in your file. Please check now.You can download the file: HERE
Missing Fill = VAR _CURVALUE = [Metric Total] VAR _CURRDAY = SELECTEDVALUE(DateTable[Date]) VAR _PREVDAY = MAXX( FILTER(ALLSELECTED(DateTable[Date]),DateTable[Date] < _CURRDAY && [Metric Total] <> BLANK()), DateTable[Date]) VAR _NEXTDAY = MINX( FILTER(ALLSELECTED(DateTable[Date]),DateTable[Date] > _CURRDAY && [Metric Total] <> BLANK()), DateTable[Date]) VAR _PREVAL = CALCULATE( [Metric Total], DateTable[Date]= _PREVDAY,ALL(DateTable)) VAR _NEXTVAL = CALCULATE([Metric Total], DateTable[Date] = _NEXTDAY,ALL(DateTable)) VAR _BLANKS = DATEDIFF(_PREVDAY,_NEXTDAY,DAY) VAR _DIFF = DIVIDE(_NEXTVAL - _PREVAL, _BLANKS ) VAR _BLANKINC = COUNTROWS( FILTER(ALLSELECTED(DateTable[Date]),DateTable[Date] < _CURRDAY && DateTable[Date] >= _PREVDAY)) VAR _INCREMENT = CALCULATE([Metric Total], DateTable[Date] =_PREVDAY) + (_DIFF * _BLANKINC) RETURN IF( ISBLANK(_CURVALUE), _INCREMENT, _CURVALUE )Additional Measure:
Metric Total = SUM(Metrics[MetricValue])________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
