Use previous data to fill in blanks in table

[REKLP]

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

[v-yulgu-msft]

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

[REKLP]

Hi @v-yulgu-msft,

I tried your code, but it doesn't seem to work when using my data model.  The following link is a sample of the data I'm working with, if you'd like to try.

https://www.dropbox.com/s/ab8lh8wcwulxrx8/Demo.pbix?dl=0

[AlB · Accepted solution]

@REKLP

m_ActiveEmployees_V2 = CALCULATE (
    SUM( ActiveEmployee[Active] );
    FILTER (
        ALL(MonthTable);
        MonthTable[Year-Month] <= MAX ( MonthTable[Year-Month] )
    )
)

[REKLP]

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

[AlB]

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]

@Anonymous  Power Query devotee 

[Anonymous]

@AlB you bet I am. Make the DAX the easiest to write as possible is my new mantra 

[AlB]

Hey @REKLP

Did you manage to make it work in the end?

[REKLP]

Hi @AlB,

It's not working for me yet.  I think I'm getting a little closer now though. I'm able to put a measure (LastNonBlankValue) into the visualization table, whenever data is not present.

So now I just need LastNonBlankValue to show the correct value.

This is what I have so far now.

lastnonblankvalue = LASTNONBLANK(CALCULATETABLE(VALUES(AppendedTables[Active]),ALLEXCEPT(AppendedTables,AppendedTables[Year-Month])), 1)

CountOfRowsInTable_v2 = 
if ([m_ActiveEmployees] = blank(),
    [lastnonblankvalue],
    [m_ActiveEmployees])

[AlB]

@REKLP

Have you tried the approach I sent you in my file?

[REKLP]

@AlB, Yes I did, it didn't work for me.  It must be the way I built the data model, that is causing the issue.

[AlB]

@REKLP

Ok but the way i built it is what you were looking for, right?  or did i misunderstand?

[AlB]

@REKLP

I think you said you have confidential data in your pbix. If you want to upload one with dummy data that reproduces the issue you're facing I could have a look. 

[REKLP]

@AlB, Yes, you understand what I'm trying to accomplish.

Here's a link to a simplified version of my pbix.

https://www.dropbox.com/s/njcjixrgkwkshtn/Demo.pbix?dl=0

[AlB]

Hi @REKLP

I had a look at your file. I think the problem probably lies with using the fact tables to filter rather than the dimension table. That is dangerous and can easily bring about unpleasant side effects. I think we've been trying  unnecessarily intricate approaches while what you require can be done in a simpler manner.

Instead of the measure that you have:

m_ActiveEmployees =
CALCULATE (
    SUM ( ActiveEmployee[Active] );
    FILTER (
        ALLEXCEPT ( ActiveEmployee; region[ED_REGION] );
        ActiveEmployee[Year-Month] <= MAX ( ActiveEmployee[Year-Month] )
    )
)

try this one and, I believe, the issue will be no more. Same approach for terminated employees.

m_ActiveEmployees_V2 = CALCULATE (
    SUM( ActiveEmployee[Active] );
    FILTER (
        ALL(MonthTable[Year-Month]);
        MonthTable[Year-Month] <= MAX ( MonthTable[Year-Month] )
    )
)

Now there are two things to take into account with this approach:

1. Do you want to do the cumulative count per year only? If so, you'll to need to add the current year to the filter because as the  V2 is now, it accumulates guess since the beginning of time in your 'MonthTable table (1955)

2. If it's actually per year you'll have to decide what to do if the first month is blank. Right now it will be left blank. What would the previous value have to be in that case?

Let me know if this does the trick. Here is the file with the new measures.

[REKLP]

Hi @AlB,

I need it to be a cumulative sum throughout the whole table.  So January of 2018 should start with what 2017 ended off with, and so on.

Here's an updated PBIX that will include 2017 data (sorry I didn't do this before).

https://www.dropbox.com/s/ab8lh8wcwulxrx8/Demo.pbix?dl=0

In the PBIX, I have two measures.  The m_ActiveEmployees gives the correct sum, it's just not showing for every month like it should.  And the m_ActiveEmployees_V2, it shows for every month, but it's not the correct sum.

[AlB]

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]

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]

@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?

[REKLP]

It doesn't seem to work.

I'm trying to understand your implementation.  Let me know if I'm wrong:
LASTNONBLANK() - returns the last nonblank value in the column for the expression

We are then feeding the LASTNONBLANK() the values from [YourMeasure]?

But [YourMeasure] is blank in some contexts, so I'm assuming that's why this didn't work?  I see you're applying the ALL() to the filter, which makes sense, so I'm not sure why this isn't working.

To clarify, [Year-Month] is indeed a number.  I'm utilizing it to help group the data.