Forum Discussion

Annu_choubey's avatar
Annu_choubey
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's avatar
    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's avatar
Fowmy
Super User
5 years ago

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 🙂

YouTube  LinkedIn

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Fowmy,

Thanks for quick reply.It's working as expected but in this  below case how can we fill all the values ?

( for Ex: we're having only date value (metric value for 9/10/2020) among all the dates like below then we need to fill all the values with that value ?

 

 

Thannks In advance

  • Fowmy's avatar
    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's avatar
      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's avatar
        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