Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
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())
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |