Forum Discussion

jaafar82's avatar
jaafar82
Frequent Visitor
8 years ago
Solved

Dax, Filling Data Gaps, Conditionally

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  ...
Need Help
Tips and Tricks
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    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] )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
Zubair_Muhammad
Community Champion
8 years ago

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] )
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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/

  • jaafar82's avatar
    jaafar82
    Frequent Visitor
    8 years ago

    Dear Zubar 

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