PQ / Data Model - Turning non consecutive days into consecutive with the value from previous day

[JoakimHGjerde]

Hi,

I have a very particular use case, where I have a data set with account balances and a data set with all calendar dates for a period. The account balances however may only contain some of the dates. I want to use return a Table or a Pivot Table with Balances for all calendar dates where the blanks are is the exact balance of the previous balance known.

Have tried to visualize a simple use case below, where I would like as an example Konto 1 to return 400 for both 04.01.2020 and 05.01.2020, etc.

Have tried to solve this using DAX but no luck. Will do this on relatively large datasets (100 accounts * 365 days) and not really to concerned whether it is solved in the Query or in DAX etc. as long as it handles the amount of data.

Any suggestions would be much appreciated.

[#2 · Accepted solution]

@JoakimHGjerde 

Better if you provide sample file instead of screenshot, but let try this way. I'd suggest Power Query

- yes, you need Date table, but don't generate in Excel sheet, you may find a lot of samples how to do such table by Power Query

- if second table is Source, reference on it, keep only Accounts column, remove duplicates, add custom column with =Date table, expand it on dates

- Merge (left join) this table with Source on Account and Date, expand Balance

- fix the table in memory by adding Index column and Group By on Accounts without aggregation

- in formula bar change each _ on each Table.FillDown(_,{"Balance"})

- expand column with grouped result

Now you may load query to data model and create PivotTable from it; or pivot query and return table to sheet, etc.

[#3]

@Sergei Baklan Thank you, that worked like a charm. And apologize for not incl. the actual sample file. Created a quick example as the data used in the model is sensitive. 

[#4]

@JoakimHGjerde , you are welcome.

As a sample I mean only exactly the same as on your screenshot, but as file, nothing more. Anyway, good to know it works, thank you for the update.