Join data based on date

[Anonymous]

Hello all,
Here is the output table that i want to make: 

end stock = stock + production

based on tgl/date

if date <=today then  stock = stock,
if date > today then  stock = end stock 

*today is 22-03-2021
so date >=22-03-2021 is future date or > today

Any idea how to implement this in power query ?
or in SQL

highly appreciated for your feedback and help!
Thank You,
Regards
Haniya

[v-jingzhang]

Hi @Anonymous 

Your description about expected output is not very clear. What does your original data look like? Which columns and data are original data and which are expected data?

If you want Today to be changed dynamically when executing the expression, you may add a custom column

if [TGL] <= Date.From(DateTime.LocalNow()) then [Stock] else [EndStock]

Regards,
Community Support Team _ Jing

[Jimmy801]

Hello @Anonymous 

not 100% clear what you exactly need. But in this example I can show you how to add a new column that based on todays date is changing the calculation.

This is the formula

if [Date]<= Date.From(DateTime.FixedLocalNow()) then [Stock] else [Stock]+[Production]

here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcixDQAwCAOwX5gRgsA3iP/faJsuDF7cLQjzNDhCVL7R21gNYufqJHaxXxbNHA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Stock = _t, Production = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Stock", Int64.Type}, {"Production", Int64.Type}}, "de-DE"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "endstock", each if [Date]<= Date.From(DateTime.FixedLocalNow()) then [Stock] else [Stock]+[Production])
in
    #"Added Custom"

and this is the output

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy