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. 

 

ITEMDATECATVALUE
A01-Jan-201682
B01-Jan-201657
C01-Jan-20165 
A02-Jan-201610 
B02-Jan-201646
C02-Jan-201691
A03-Jan-201673
B03-Jan-20168 
C03-Jan-20164 
A04-Jan-20166 
B04-Jan-20166 
C04-Jan-201662
A05-Jan-201682
B05-Jan-201655
C05-Jan-20165 
A06-Jan-201610 
B06-Jan-201646
C06-Jan-201691
A07-Jan-201673
B07-Jan-20168 
C07-Jan-20164 
A08-Jan-20166 
B08-Jan-20166 
C08-Jan-201661
A09-Jan-201682
B09-Jan-201654
C09-Jan-20165 
A10-Jan-201610 
B10-Jan-201646
C10-Jan-201691
A11-Jan-201673
B11-Jan-20168 
C11-Jan-20164 
A12-Jan-20166 
B12-Jan-201665
C12-Jan-201662

 

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

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

8 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      jaafar82
      Frequent Visitor
      8 years ago

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

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    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's avatar
      jaafar82
      Frequent Visitor
      8 years ago

      Yes i got the solution and just accepted as a solution 

       

       

      Thank you very much