Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 )

 

IdPost DateEnd date
103/01/202010/27/2020
209/01/201909/01/2020

 

Thanks,

Shubham

1 ACCEPTED SOLUTION
v-yuaj-msft
Community Support
Community Support

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:

v-yuaj-msft_0-1604020005443.png

 

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.

 

 

 

View solution in original post

2 REPLIES 2
v-yuaj-msft
Community Support
Community Support

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:

v-yuaj-msft_0-1604020005443.png

 

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
Super User
Super User

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())

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.