Forum Discussion

Microsoft Employee

5 years ago

Solved

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

Need Help

Fowmy

5 years ago

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 🙂

YouTube LinkedIn

Fowmy

Super User

5 years ago

Anonymous

In this scenario, you have filtered Asset name by AC and there is only on value on 9/10/2020, since there is no previous value it is having blanks. I have modified the code so in case of no previous values, the next available value will fill backward.

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),
    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 🙂

YouTube LinkedIn

Anonymous

Not applicable

5 years ago

Hi Fowmy

If the metric value is 0 then it is not working as expected. I've been applied the filters in the PBIX file.

Filters:

Asset: Profile

manme:teanet

Here is the file:

https://1drv.ms/u/s!Au-aOkl1BoHugijnugHCN0fzY8jq?e=9TWQct

Thanks In Advance

Fowmy
Super User
5 years ago
Anonymous

What should be the condition for zero?

Try the following change after the RETURN part

IF( ISBLANK(_CURVALUE) || _CURVALUE = 0, 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 🙂
YouTube LinkedIn
- Anonymous
  Not applicable
  5 years ago
  Hi Fowmy,
  
  Our current measure working all the cases except where lastnonblankvalue is 0 . I've explained below with example .
  The filling values should be from max to min value.
  Sample table
  DATE VALUE
  Day 1 10
  Day 2
  Day 3
  Day 4 0
  
  Ps: The filling values should be from max to min value (10 -0)
  
  If lastnonblankvalue (Day4) is 0 then we should decrement the value
  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 10 and min value is 0 and countofblanks is: 1 then 10-0/2+1=3.3
  Condition 2: Condition 1 output value should be subtract to all blank values (firstnonblank value) )
  (Ex: condition 1 output is : 3.3 - day 1 value)
  Day 1 - 10
  Day 2 -10(day 1 value)-3.3=6.7
  day 3 - 6.7(day 2 value)-3.3=3.4
  Day 4 - 0
  
  above scenerio is not working in our current measure, expect these all cases should be working as expected.
  
  Example from Model
  Currently missing fill measure retuning below values but expected values are written in blue colour.
  
  Here is the path : https://1drv.ms/u/s!Au-aOkl1BoHugijnugHCN0fzY8jq?e=xDyQXq
  
  Please let me know if you need any info.
  
  Thanks In Advance.
  - Fowmy
    Super User
    5 years ago
    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 🙂
    YouTube LinkedIn

Forum Discussion

How to write measure to get incremental value for missing value for particular date?

Related content

Increment Refresh

Incremental refresh

Incremental Refresh

Incremental Refresh on Dataflow sourced from SharePoint

Incremental refresh