cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

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

@vasu538 

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

View solution in original post

9 REPLIES 9
Highlighted
Community Champion
Community Champion

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

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

Fowmy_0-1601545808005.png

 

________________________

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

 



Highlighted
Frequent Visitor

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

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.

 

 

 

Highlighted
Community Champion
Community Champion

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

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

Highlighted
Frequent Visitor

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

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 ?

 

cp.PNG

 

Thannks In advance

Highlighted
Community Champion
Community Champion

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

@vasu538 

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

Highlighted
Frequent Visitor

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

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

 

Highlighted
Community Champion
Community Champion

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

@vasu538 

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

Highlighted
Frequent Visitor

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

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.

 

Capture.PNG

 

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.

 

 

 

Highlighted
Community Champion
Community Champion

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

@vasu538 

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

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors