Forum Discussion
Helper IPopulate a date field based on a criteria
Hi ,
I have a bit of a dilema regarding creation of a new date field based on two otehr date fields values.
For example - If i have an item with a billing date set to 30th September 2023 and alos has a billing entered date set to the 1st or 2nd day for October I need the allocation date to be set to the billing date.
If the billing date is 30th September and the billing entered date is 20th September, then the allocated date should be the 30th Sept
However if the billing date is the 30th September 2023 and the billing entered date is 4th October 2023 then the allocated date should be 4th October.
Basically ther are 2 days at the beginning of each month whereby billing entered dates can be added for itmes with billing dates in the previous month
| Billing date | Billing entered date | Allocation date (non calculated value) |
| 28/10/2023 | 01/11/2023 | 28/10/2023 |
| 28/10/2023 | 02/11/2023 | 28/10/2023 |
| 30/09/2023 | 20/09/2023 | 30/09/2023 |
| 29/09/2023 | 04/10/2023 | 04/10/2023 |
| 01/02/2023 | 02/02/2023 | 01/02/2023 |
4 Replies
- lbendlin
Super User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLQNzTQNzIwMlbSUTIw1Dc0hHBiddDljFDkjA30DSxhckYIDlifJZKcgQncEJAc0AqgSQgzYZzYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Billing date" = _t, #"Billing entered date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Billing date", type date}, {"Billing entered date", type date}},"en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type", "Allocation date", each if List.Contains({1,2},Date.Day([Billing entered date])) then [Billing date] else List.Max({[Billing date],[Billing entered date]})) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
James_MaThanks for this, the problem is that the data I gave are just for a a demo purpose, not the actual data table
- lbendlin
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
