Help with filling in values based on previous data

[AusJon]

Hi, I need some help with creating charts based on data that is only stored on change. This makes the series incomplete and when generating a chart by date I can only get it to display the totals for each day based off what was reported in on that day, not estimate using prior data. We also have no end date for the data, we need to assume everything in the future past the timestamp is set the same way.

Here is some example data

Name	Compliant	Timestamp
ItemA	FALSE	2018-09-04
ItemB	FALSE	2018-09-04
ItemA	TRUE	2018-09-05
ItemA	FALSE	2018-09-06
ItemB	TRUE	2018-09-08
ItemA	TRUE	2018-09-08
ItemB	FALSE	2018-09-10

As items only report in when they change, we know that ItemB was false on every day from the 4th until the 8th when it changed to TRUE. But doing a graph looking for all FALSE items broken down by day ends up looking something like this:

Sorry if this has been asked before or an easy fix, I am still very new to PowerBI. I found a lot of posts about slowly changing dimensions and I think this is simular but doesn't include the end date for the data so a lot of the examples I couldn't get to work.

Do I need to fill in the blank days with the correct data so we have a record for every day in the table? Or can it be simulated with some DAX code?

[v-lili6-msft]

hi,@AusJon

    After my research, you can do these like below:

   Step1:

Duplicate the basic table and add Index column for two table

Basic table is from 0 and increment is 1, Duplicate table is from 1 and increment is 1

Step 2:

Add a custom column for Duplicate table

Step3:

Merge these two tables

Step4:

Expand the table and sort by column Index

Step5:

Add the index column from 1 and increment is 1

Rename column Timestamp with Sart and rename Table1 (2).Timestamp with End 

Close&Apply

Step6:

Create a date table by the function like: CALENDARAUTO( )

Add a column for merge table

Tag = IF(CALCULATE(MIN(Merge1[Index.1]),FILTER(Merge1,Merge1[Start]<=Merge1[End]&&EARLIER(Merge1[Index])=Merge1[Index]))=Merge1[Index.1]||CALCULATE(MAX(Merge1[Start]),FILTER(Merge1,Merge1[Name]=EARLIER(Merge1[Name])))=Merge1[Start],Merge1[Index.1])

Step7:

Create a calculate table

Table = FILTER(CROSSJOIN(Merge1,'Date'),OR('Date'[Date]>=Merge1[Start]&&'Date'[Date]<Merge1[End]&&Merge1[Tag]>0,CALCULATE(MAX(Merge1[Start]),FILTER(Merge1,Merge1[Name]=EARLIER(Merge1[Name])))='Date'[Date]&&Merge1[Maxdate]=Merge1[Start]))

Step8:

Rename the messy column, then drag filed from New Table.

Result:

Here is demo, please try it.

https://www.dropbox.com/s/m2z2y9lpp3pfejq/Help%20with%20filling%20in%20values%20based%20on%20previou...

Best Regards,

Lin

[AusJon]

Thanks for the detailed explination Lin. Explains why I did get suck, it isn't simple!

I'll study your examples and hopefully will be able to get it working.