Forum Discussion
Dax Column creation based on latest date
Hello,
I am new to power bi and need some help. I am trying to create a column based on last login date. If the last login date is within a month ago, called active. If it is 2 months ago, called two months ago etc.
Here is my dax but it’s showing the wrong result except for "Active".
Column =
IF( MONTH(TODAY())-1 <= MONTH((DATEADD(Employee[Last Login],-1,MONTH))), "Active",
IF( MONTH(TODAY())-2 <= MONTH((DATEADD(Employee[Last Login],-2,MONTH))), "2months ago",
IF( MONTH(TODAY())-3 <= MONTH((DATEADD(Employee[Last Login],-3,MONTH))), "3months ago",
IF( MONTH(TODAY())-6 <= MONTH((DATEADD(Employee[Last Login],-6,MONTH))), "6months ago",
IF( MONTH(TODAY())-12 <= MONTH((DATEADD(Employee[Last Login],-12,MONTH))), "Inactive")))))
hre the reference in the formula:
https://learn.microsoft.com/en-us/dax/datediff-function-dax
try somehting like this:
Column =
IF( DATEDIFF ( StartDate, EndDate, MONTH ) <= 1 , "Active",
IF( DATEDIFF ( StartDate, EndDate, MONTH ) <= 2, "2months ago",
IF( DATEDIFF ( StartDate, EndDate, MONTH ) <= 3, "3months ago",
IF( DATEDIFF ( StartDate, EndDate, MONTH ) < 12, "6months ago",
IF( DATEDIFF ( StartDate, EndDate, MONTH ) >= 12, "Inactive")))))
2 Replies
hre the reference in the formula:
https://learn.microsoft.com/en-us/dax/datediff-function-dax
try somehting like this:
Column =
IF( DATEDIFF ( StartDate, EndDate, MONTH ) <= 1 , "Active",
IF( DATEDIFF ( StartDate, EndDate, MONTH ) <= 2, "2months ago",
IF( DATEDIFF ( StartDate, EndDate, MONTH ) <= 3, "3months ago",
IF( DATEDIFF ( StartDate, EndDate, MONTH ) < 12, "6months ago",
IF( DATEDIFF ( StartDate, EndDate, MONTH ) >= 12, "Inactive")))))