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's just no data there.  I don't want to put a zero either.

 

I also tried putting zero + the calculated measure, but that doesn't work either.

 

 

CountOfRowsInTable = 
IF(
    ISBLANK(
        CALCULATE (
            SUM( AppendedTables[Active] ),
            FILTER (
                ALLEXCEPT(AppendedTables, v_infohr_region[ED_REGION], v_infohr_plant[plant]),
                AppendedTables[Year-Month] <= MAX ( AppendedTables[Year-Month] )
            )
        )
    ),
    CALCULATE (
            SUM( AppendedTables[Active] ),FILTER(AppendedTables,0=0)
        ),
    CALCULATE (
            SUM( AppendedTables[Active] ),
            FILTER (
                ALLEXCEPT(AppendedTables, v_infohr_region[ED_REGION], v_infohr_plant[plant]),
                AppendedTables[Year-Month] <= MAX ( AppendedTables[Year-Month] )
            )
    )
)
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] )
    )
)

25 Replies

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

    Hi REKLP

    1. Is CountOfRowsInTable a measure  or calculated column?

    2. What are you trying to do with this bit?

     

    CALCULATE (
            SUM( AppendedTables[Active] ),FILTER(AppendedTables,0=0)

    3. We're assuming that this is the correct way to calculate what you want when the result in not blank, right? 

     

    CALCULATE (
            SUM( AppendedTables[Active] ),
            FILTER (
                ALLEXCEPT(AppendedTables, v_infohr_region[ED_REGION], v_infohr_plant[plant]),
                AppendedTables[Year-Month] <= MAX ( AppendedTables[Year-Month] )
            )
        )

     

     

     

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

      1. CountOfRowsInTable is a measure.

      2. I was trying to get an arbitrary number to show.  I just wanted to see if it was possible to get something to populate, then I would work off of that.  But in the end, I want the previous calculated measure to show.

      3. Correct.

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

        REKLP

         

        I think you could make use of LASTNONBLANK, as you are looking to well... get the value for the last non-blank row. How about something along the lines of :

         

        1. We make use of you described calculation for the row as a  measure (I haven't touched your code):

         

        YourMeasure =
CALCULATE (
    SUM ( AppendedTables[Active] ),
    FILTER (
        ALLEXCEPT ( AppendedTables, v_infohr_region[ED_REGION], v_infohr_plant[plant] ),
        AppendedTables[Year-Month] <= MAX ( AppendedTables[Year-Month] )
    )
)

         

        2. Define CountOfRowsInTable based on the following pattern. Supposedly [Year-Month] is a number although I believe the code would work too if it is text.  

         

        CountOfRowsInTable =
IF (
    ISBLANK ( [YourMeasure] );
    LASTNONBLANK (
        CALCULATETABLE (
            VALUES ( AppendedTables[Year-Month] );
            FILTER (
                ALL ( AppendedTables[Year-Month] );
                AppendedTables[Year-Month] < SELECTEDVALUE ( AppendedTables[Year-Month] )
            )
        );
        [YourMeasure]
    );
    [YourMeasure]
)

         

        Does that help?

  • 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

      Hi REKLP

      Here's the file.

      Another version of CountOfRowsInTable, leaner but likely more computationally expensive:

       

      CountOfRowsInTable_v2 =
LASTNONBLANK (
    CALCULATETABLE (
        VALUES ( AppendedTables[Year-Month] );
        FILTER (
            ALL ( AppendedTables[Year-Month] );
            AppendedTables[Year-Month] <= SELECTEDVALUE ( AppendedTables[Year-Month] )
        )
    );
    [YourMeasure]
)
      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous  Power Query devotee  :smileywink:

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