topic Re: Populate a date field based on a criteria in DAX Commands and Tips

Populate a date field based on a criteria

James_Ma — Fri, 27 Oct 2023 14:41:45 GMT

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

Re: Populate a date field based on a criteria

lbendlin — Sat, 28 Oct 2023 21:14:59 GMT

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".

Re: Populate a date field based on a criteria

James_Ma — Mon, 30 Oct 2023 13:58:46 GMT

Thanks for this, the problem is that the data I gave are just for a a demo purpose, not the actual data table

Re: Populate a date field based on a criteria

lbendlin — Mon, 30 Oct 2023 15:05:34 GMT

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

Re: Populate a date field based on a criteria

James_Ma — Mon, 30 Oct 2023 15:56:18 GMT

Guess the below is the same as above right?