Create a column from Post date column till the current date or one year

[Anonymous]

Hi Team,

I have column called Post date which has  2 values 03/01/2020 and 09/01/2019 .I wanted a calucualted column using Dax so that

if the date is less than a year then value will be today's date ( 10/27/2020 ) and if it is more than year then it will show that date plus one year ( 09/01/2020 )

Id	Post Date	End date
1	03/01/2020	10/27/2020
2	09/01/2019	09/01/2020

Thanks,

Shubham

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a calculated column as follows.

Command:

End Date = IF(

                      DATEDIFF('Table'[Post Date],TODAY(),YEAR)=0,

                      TODAY(),

                      DATE(YEAR('Table'[Post Date])+1,MONTH('Table'[Post Date]),DAY('Table'[Post Date]))

                      )

Or

End Date =

               var maxdate =DATE(YEAR([Post Date])+1,MONTH([Post Date]),DAY([Post Date]))

               return

               IF(

                     TODAY()<maxdate,

                     TODAY(),

                     maxdate

                   )

Result:

I hope my suggestion can give you some help.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Does it have to be a calculated column or can it be a Power Query transform too?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1jcw1DcyMDIAcgwN9I3MIZxYnWglI5C8JUTe0BKJA5KPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Post Date" = _t, #"End date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Post Date", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each 
if [Post Date] < Date.From(Date.AddYears(DateTime.FixedLocalNow(),-1)) 
then Date.AddYears([Post Date],1) 
else Date.From(DateTime.FixedLocalNow()))
in
    #"Added Conditional Column"

DAX:

Column = IF('Table'[Post Date]<EDATE(TODAY(),-12),EDATE('Table'[Post Date],12),TODAY())