Forum Discussion

Annu_choubey's avatar
Annu_choubey
Icon for Microsoft Employee rankMicrosoft 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

Anonymous's avatar
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's avatar
Fowmy
Icon for Super User rankSuper 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