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