Create a table with loops

[dujhe]

Hi,

I have a dataset of completed responses to a web form which is completed by our providers. Within the dataset is the providers unique ID, the date the form was completed and the value I need to report on. The provider can complete the form as many times as they like on whatever date they like.

I have been asked to report on the trend of this value. What I am trying to do is produce a table that will give a date column starting from the earliest date in the above table and then list every day until today and then repeat this for each Unique ID.

I would like to take the most recent value for each Unqiue ID when there isn't a response for that provider on the given day. To try and explain this a bit clear please see the table before, which is what I would ideally like to get to. You can see Unique ID 1, has a value of 1 until the 26/05/2020 when they submitted a new value of 2 and that is now being reported until 30/05/2020 once they submittted another return.

I have created a seperate date table and joined my dataset to that and then created the following measure 

I then created a matrix with the date from the date table and used this measure as the value but it was just giving me a total and not a value for each date.

Any help anyone can provider would be greatly appreciated.

[mahoneypat]

Making tables like this is easier in M/Query Editor IMO, so that's how I approached it.  I used similar data, and I believe this gets your desired result.  Please paste this into a blank query to see one approach on doing this.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLVN9Q3MjAyADINlWJ1YIImMEEjJEEzVJVGYEEjbIKmSNpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", Int64.Type}, {"Date", type date}, {"Value", Int64.Type}}),
Custom1 = List.Dates(List.Min(#"Changed Type"[Date]), Duration.Days(List.Max(#"Changed Type"[Date])-List.Min(#"Changed Type"[Date]))+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date2"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date2", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.Distinct(#"Changed Type"[Unique ID])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Unique ID"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Unique ID", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type2", {"Date2", "Unique ID"}, #"Changed Type", {"Date", "Unique ID"}, "Merged", JoinKind.LeftOuter),
#"Expanded Merged" = Table.ExpandTableColumn(#"Merged Queries", "Merged", {"Value"}, {"Value"}),
#"Grouped Rows" = Table.Group(#"Expanded Merged", {"Unique ID"}, {{"AllRows", each _, type table [Date2=date, Unique ID=number, Value=number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FillDown([AllRows],{"Value"})),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date2", "Value"}, {"Date2", "Value"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Value", Int64.Type}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Date2", "Date"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Date", type date}})
in
#"Changed Type4"

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[AlB · Accepted solution]

Hi @dujhe 

Agree resorting to M is an option worth considering. In this case, however, the required DAX code is not excessively complex. Create a calculated table; Table1 is the initial table you show:

NewTable = 
VAR startDate_ = MIN(Table1[Date])
VAR endDate_ = TODAY()
VAR tab0_ = CROSSJOIN(DISTINCT(Table1[ID]),CALENDAR(startDate_,endDate_))
RETURN 
ADDCOLUMNS(tab0_, "Value", 
            VAR latestDate_ = CALCULATE(MAX(Table1[Date]),Table1[Date] <=EARLIER([Date]), Table1[ID] = EARLIER([ID]))
            RETURN CALCULATE(MAX(Table1[Value]),Table1[Date] = latestDate_, Table1[ID] = EARLIER([ID])))

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@dujhe 

another version in M. I think it is likely the version in DAX will be faster (if your tables are large). BaseTable1 is the first table you showed

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy0Dcw1TcyMDIAcgyVYnUgwsaYwkYg1cZIwkZgYWMQywhJ2BgsbAISNsVqtpEZqiGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AvailableDates", each Table.FromColumns({[Date],[Value]}, {"Date", "Value"}) }}),
    allDates_ = List.Dates(List.Min(BaseTable1[Date]), Number.From(Date.From(DateTime.LocalNow()) - List.Min(BaseTable1[Date])) +1,#duration(1,0,0,0)),
    #"Added Column" = Table.AddColumn(#"Grouped Rows", "Missingdates", each let missingdatesL_= List.Difference(allDates_,[AvailableDates][Date]) in Table.FromColumns({missingdatesL_,List.Repeat({null},List.Count(missingdatesL_))}, {"Date", "Value"}) ),
    #"Added Custom" = Table.AddColumn(#"Added Column", "AllDates", each Table.Sort(Table.Combine({[AvailableDates],[Missingdates]}),{{"Date", Order.Ascending}})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Filldown", each Table.FillDown([AllDates],{"Value"})),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"AvailableDates", "Missingdates", "AllDates"}),
    #"Expanded Filldown" = Table.ExpandTableColumn(#"Removed Columns1", "Filldown", {"Date", "Value"}, {"Date", "Value"})
in
    #"Expanded Filldown"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers