Reference the last non blank value from the sequential period

[deedeedudu]

Hi ,

I want to create a table in Power BI with continous set of data. The problem is the source has some missing time periods.

Month	Contacts
5/1/23	200
6/1/23	230
9/1/23	400
10/1/23	410
11/1/23	420
12/1/23	500
1/1/24	560

What i want is the below:

Month	Contacts
5/1/23	200
6/1/23	230
7/1/23	230
8/1/23	230
9/1/23	400
10/1/23	410
11/1/23	420
12/1/23	500
1/1/24	560

I created a measure as following:

Contacts 1 = IF(ISBLANK(MAX(Contacts[Contacts])), CALCULATE(MAX(Contacts[Contacts]),PREVIOUSMONTH('CALENDAR'[Date])), MAX(Contacts[Contacts]))

But it is giving value for the first non blank period and is populating as following.

Month	Contacts
5/1/23	200
6/1/23	230
7/1/23	230
8/1/23
9/1/23	400
10/1/23	410
11/1/23	420
12/1/23	500
1/1/24	560

[deedeedudu]

@ppm1 Can you please modify your suggestion as per the additional column condition?

[ppm1]

You can do it with a measure but you can also do it in your query (to simplify your DAX and enable other analyses too). Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. It starts with your full list of dates, merges in your first table and then does a fill down on the Contacts column.

let
    Source = List.Transform({0..8}, each Date.AddMonths(#date(2023,5,1), _)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Date"}, Contacts, {"Month"}, "Contacts", JoinKind.LeftOuter),
    #"Expanded Contacts" = Table.ExpandTableColumn(#"Merged Queries", "Contacts", {"Contacts"}, {"Contacts.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Contacts",{{"Date", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Contacts.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Filled Down",{{"Contacts.1", "Contacts"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Contacts", Int64.Type}})
in
    #"Changed Type1"

Pat

[deedeedudu]

What if i have additional column such as following:

Month	Customer	Contacts
5/1/23	AA	200
6/1/23	AA	230
9/1/23	AA	400
10/1/23	AA	410
11/1/23	AA	420
12/1/23	AA	500
1/1/24	AA	560
10/1/23	BB	50
11/1/23	BB	60
1/1/24	BB	90

Intended outup is like:

Month	Customer	Contacts
5/1/23	AA	200
6/1/23	AA	230
7/1/23	AA	230
8/1/23	AA	230
9/1/23	AA	400
10/1/23	AA	410
11/1/23	AA	420
12/1/23	AA	500
1/1/24	AA	560
10/1/23	BB	50
11/1/23	BB	60
12/1/23	BB	60
1/1/24	BB	90