How to repeat values until superseded?

[srholmes]

Hi,

I would like to upload an Excel file with budget data for a given month (e.g., Jan 2020 - $4,000) and have the data be repeated for all subsequent months until it is superseded (e.g., May 2020 - $4,500). What is th ebest way to do this, other than repeating it in Excel?

I think this is a simple problem but I can't find an answer after quite a bit of searching.

Many thanks

[amitchandak]

@srholmes , Not very clear

you can create a calendar and put logic based on month and do it

Target = if(Eomonth('Date'[Date],0)<=Eomonth(TODAY(),-1) && Data[Date]= Eomonth('Date'[Date],0),4000,4500)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[srholmes]

Thanks for the answers everyone.

Here is a sample of what I am trying to achieve:

Demo data 

There are several categories and I would like to create a monthly budget for each category that is repeated each month based on the input values until I change them at some later review point. I will have many more categories so would prefer not to have to do them all manually.

Also I do have the date query set up too and will link to it.

Many thanks

Simon

[mahoneypat · Accepted solution]

Thanks for the demo data.  Here is the M code to see one way to accomplish this one.  This one is a good example of how powerful M is.  Filling down whole tables!

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVA9D4IwEP0rTWcSKArqqolxMRqDE2Go0EiTekfaEuXf2w5EPlZc7vveu3t5TlnIwjhiOxrQ1dqZM4eKW9Sdi0/YGtF7spdKGZcdsIVSKpLxj59HsLXy09uIFsEYkDFnjxK4W+BqEu9Ra3xLeJKQ3ETDu5cA6wnuYETZalERhRyI7nsjNpbM6dJ48kCmOZgGtV+92FpoMqxc24eS5aj0w98M4eNoAXmSGeA/5Unn9y8qj/um+AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category_ID = _t, Category_L0 = _t, Category_L1 = _t, Category_L2 = _t, Category_L3 = _t, Recurrence_Frequency = _t, Amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category_ID", Int64.Type}, {"Category_L0", type text}, {"Category_L1", type text}, {"Category_L2", type text}, {"Category_L3", type text}, {"Recurrence_Frequency", type text}, {"Amount", Int64.Type}}),
GroupedRows = Table.Group(#"Changed Type", {"Date"}, {{"AllRows", each _, type table [Date=date, Category_ID=number, Category_L0=text, Category_L1=text, Category_L2=text, Category_L3=text, Recurrence_Frequency=text, Amount=number]}}),
Custom1 = List.Transform({0..23}, each Date.AddMonths(#date(2019,1,1), _)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Date"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Date"}, GroupedRows, {"Date"}, "Changed Type1", JoinKind.LeftOuter),
#"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type1", {"AllRows"}, {"AllRows"}),
#"Filled Down" = Table.FillDown(#"Expanded Changed Type1",{"AllRows"}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Filled Down", "AllRows", {"Category_ID", "Category_L0", "Category_L1", "Category_L2", "Category_L3", "Recurrence_Frequency", "Amount"}, {"Category_ID", "Category_L0", "Category_L1", "Category_L2", "Category_L3", "Recurrence_Frequency", "Amount"})
in
#"Expanded AllRows"

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

Regards,

Pat

[parry2k]

@srholmes Solution provided by @mahoneypat will work but the only concern is to add # of rows for each category, and it will bloat your model whereas the same target value can be achieved using measures (without adding extra rows). If there are fewer categories, yes use @mahoneypat solutions but if there are too many categories, you might have to rethink your approach.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[srholmes]

Thanks for the replies @parry2k and @mahoneypat - I have tried the suggestion and managed to adapt it to the local excel sheet that I have and it has worked - I now have a row for each category in each month. Thanks!

My model will grow over time so I may end up with many categories so it would be helpful to know how to do it as a measure. Are you suggesting I woul d try to create an expression that would equal the most recent number loaded in for that category, and define it as 'Category Budget for the month' or something like that?

Thanks

Simon

[parry2k]

@srholmes that's exactly it, no need to create a row for each month and similar you can achieve by measure.

[mahoneypat]

You can do this in the query editor by starting with a list of all possible dates/months, convert to table, and then merging/expanding in the data/query you do have, and then doing a Fill Down.  In your case, Jan-Apr would have the same values and then be "replaced" in May with the new number.

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

Regards,

Pat

[parry2k]

@srholmes it can be easily transformed in Power Query but query steps will depend on how your data looks like. Why not post a sample data in a table format, from there it will be easy to provide to fill the missing target data based on the raw data.