Filling blank dates with previous values

[crispybc]

I have the following tables:

My goal is that I need to see the STATUS and ASSIGNEE_ID of an ID at any given date after the CREATED_DATE

I have the following DAX:

Test = VAR currentDate = MAX('Date_Table'[Date])
RETURN
    CALCULATE (
        COUNTROWS ( History ),
        FILTER (
            History,
            History[UPDATED_AT] = CALCULATE(MAX(History[UPDATED_AT]),History[UPDATED_DATE] = currentDate, GROUPBY(History,History[ID]))                       
        )
    )

However this obviously can't count rows that don't exists. I would like to fill in the blank dates (dates where no UPDATE_DATE exists for an ID) using the previous row values. For example the below should have all the ampty dates filled.

Need some guidance on how to go about this, I think maybe countrows may not be the right way, however I would like to still be able to filter the visualisations on the status/assigne_id

[v-stephen-msft]

Hi @crispybc ,

Here's my solution.

Sample data

1.Create the calendar table named 'Date_Table'.

Date_Table = CALENDAR(MIN('History'[UPDATED_AT]),MAX('History'[UPDATED_AT]))

2.Create the first measure to count rows.

Measure = COUNTROWS('History')

3.Create the second measure to fill blank values.

Measure_Filldown =
VAR _date =
    CALCULATE (
        MAX ( 'Date_Table'[Date] ),
        FILTER (
            ALL ( 'Date_Table' ),
            [Date] <= MAX ( 'Date_Table'[Date] )
                && [Measure] <> BLANK ()
        )
    )
RETURN
    CALCULATE ( [Measure], FILTER ( ALL ( 'Date_Table' ), [Date] = _date ) )

4.The result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@crispybc ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

I think you need Last Day Non Continuous  and take coalease with current day date

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...