Forum Discussion

REKLP's avatar
REKLP
Icon for Helper I rankHelper I
7 years ago
Solved

Use previous data to fill in blanks in table

I have gaps between my data.    I'd like to fill them in with the previous data, for example.   I tried using an if statement (shown below), but it doesn't seem to work because there'...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    REKLP

     

    m_ActiveEmployees_V2 = CALCULATE (
    SUM( ActiveEmployee[Active] );
    FILTER (
        ALL(MonthTable);
        MonthTable[Year-Month] <= MAX ( MonthTable[Year-Month] )
    )
)
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi REKLP,

 

Based on my assumption, I test with below sample dataset. The left two visuals show data table, they are linked by [Year_Month] field. The right visual is the expected result.

 

CountofrowsinTable = COUNT(Appendedtable[Active])

result =
VAR previousnonblankvalue =
    IF (
        [CountofrowsinTable] = BLANK (),
        CALCULATE (
            MIN ( Appendedtable[Year_Month] ),
            FILTER (
                ALLSELECTED ( yearmonth ),
                yearmonth[Year_Month] > MAX ( yearmonth[Year_Month] )
            )
        ),
        BLANK ()
    )
RETURN
    IF (
        [CountofrowsinTable] = BLANK (),
        CALCULATE (
            [CountofrowsinTable],
            FILTER (
                ALLSELECTED ( yearmonth[Year_Month] ),
                yearmonth[Year_Month] = previousnonblankvalue
            )
        ),
        [CountofrowsinTable]
    )

 

Best regards,

Yuliana Gu

 

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    REKLP

     

    m_ActiveEmployees_V2 = CALCULATE (
    SUM( ActiveEmployee[Active] );
    FILTER (
        ALL(MonthTable);
        MonthTable[Year-Month] <= MAX ( MonthTable[Year-Month] )
    )
)
    • REKLP's avatar
      REKLP
      Icon for Helper I rankHelper I
      7 years ago

      AlB, Thanks so much for your help!  That worked perfectly.