Dax, Filling Data Gaps, Conditionally

[jaafar82]

Dear Gentlemen 

Hi, I have a table that looks like the below table. 

ITEM	DATE	CAT	VALUE
A	01-Jan-2016	8	2
B	01-Jan-2016	5	7
C	01-Jan-2016	5
A	02-Jan-2016	10
B	02-Jan-2016	4	6
C	02-Jan-2016	9	1
A	03-Jan-2016	7	3
B	03-Jan-2016	8
C	03-Jan-2016	4
A	04-Jan-2016	6
B	04-Jan-2016	6
C	04-Jan-2016	6	2
A	05-Jan-2016	8	2
B	05-Jan-2016	5	5
C	05-Jan-2016	5
A	06-Jan-2016	10
B	06-Jan-2016	4	6
C	06-Jan-2016	9	1
A	07-Jan-2016	7	3
B	07-Jan-2016	8
C	07-Jan-2016	4
A	08-Jan-2016	6
B	08-Jan-2016	6
C	08-Jan-2016	6	1
A	09-Jan-2016	8	2
B	09-Jan-2016	5	4
C	09-Jan-2016	5
A	10-Jan-2016	10
B	10-Jan-2016	4	6
C	10-Jan-2016	9	1
A	11-Jan-2016	7	3
B	11-Jan-2016	8
C	11-Jan-2016	4
A	12-Jan-2016	6
B	12-Jan-2016	6	5
C	12-Jan-2016	6	2

and i'd like to fill the blank value . based on the Items and date , 

i Try 2 solutions but both are fill the gap basd on the latest max value instead of last non blank and here is a secreen shot of the result .  below i make filter based on item to check the results. 

here is is the first formula i used . 

Test1 = 
IF (
    Table1[VAL] = BLANK (),
    CALCULATE (
        LASTNONBLANK ( Table1[VAL], Table1[VAL] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[ITEM] ), Table1[DATE] <= EARLIER ( Table1[DATE] ) )
    ),
    Table1[VAL]
)

and this is the second one 

Test2 = 
IF(ISBLANK(Table1[VAL]),
CALCULATE(LASTNONBLANK(Table1[VAL],0),
FILTER(Table1,
Table1[DATE]<EARLIER(Table1[DATE])&&
Table1[ITEM]=EARLIER(Table1[ITEM])&&
[DATE]=LASTDATE(Table1[DATE])&&
not(ISBLANK(Table1[VAL]))
)
),Table1[VAL])

and i'd like to reach out to the below result . "Exp Result"

Thank you for your Help & support

[Zubair_Muhammad · Accepted solution]

@jaafar82

Try this one

Test1 =
VAR MyDate =
    IF (
        Table1[VALUE] = BLANK (),
        CALCULATE (
            MAX ( Table1[DATE] ),
            FILTER (
                ALLEXCEPT ( Table1, Table1[ITEM] ),
                Table1[DATE] < EARLIER ( Table1[DATE] )
                    && Table1[VALUE] <> BLANK ()
            )
        )
    )
RETURN
    IF (
        Table1[VALUE] = BLANK (),
        CALCULATE (
            SUM ( Table1[VALUE] ),
            FILTER ( ALLEXCEPT ( Table1, Table1[ITEM] ), Table1[DATE] = MyDate )
        ),
        [VALUE]
    )

[v-piga-msft]

Hi @jaafar82.

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[jaafar82]

Yes i got the solution and just accepted as a solution 

Thank you very much 

[Anonymous]

Hi @jaafar82,

You could easily use the Fill feature in "Edit Queries" instead of DAX. It will return exactly what you are looking for.

Br,

T

[jaafar82]

Thank you for your solution "but i need it in DAX" 🙂

[Zubair_Muhammad · Accepted solution]

@jaafar82

Try this one

Test1 =
VAR MyDate =
    IF (
        Table1[VALUE] = BLANK (),
        CALCULATE (
            MAX ( Table1[DATE] ),
            FILTER (
                ALLEXCEPT ( Table1, Table1[ITEM] ),
                Table1[DATE] < EARLIER ( Table1[DATE] )
                    && Table1[VALUE] <> BLANK ()
            )
        )
    )
RETURN
    IF (
        Table1[VALUE] = BLANK (),
        CALCULATE (
            SUM ( Table1[VALUE] ),
            FILTER ( ALLEXCEPT ( Table1, Table1[ITEM] ), Table1[DATE] = MyDate )
        ),
        [VALUE]
    )

[Zubair_Muhammad · Accepted solution]

@jaafar82

This one should also work

Test2 =
VAR temp =
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[ITEM] = EARLIER ( Table1[ITEM] )
                && Table1[DATE] < EARLIER ( Table1[DATE] )
                && Table1[VALUE] <> BLANK ()
        ),
        [DATE], DESC
    )
RETURN
    IF ( ISBLANK ( Table1[VALUE] ), MINX ( temp, [VALUE] ), [VALUE] )

[jaafar82]

Dear Zubar 

Many thanks for your solutions. both gives me correct results. 

[Zubair_Muhammad]

@jaafar82

Actually

"FirstNonBlank /LastNonBlank return the first/last value respectively in the column…..after sorting the column in its native Ascending Order….column, filtered by the current context, where the expression is not blank."

SO when you use

LASTNONBLANK ( Table1[VAL], Table1[VAL] )

It returns the lastvalue sorting values in their own order and NOT by the order of their dates

Check this post as well

http://www.excelnaccess.com/using-firstnonblank-lastnonblank-in-dax/