Forum Discussion
How to repeat values until superseded?
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
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
Thanks for the answers everyone.
Here is a sample of what I am trying to achieve:
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
SimonThanks 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
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!
