Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have created a calculated column that counts number of days a specific serial number has been in a specific status, "Redress".
This works pretty good, but I have encountered a challenge: As the DAX only calculates days BETWEEN "Redress In" and Redress Out" it will fail to count the days for the serial numbers which are in "Redress In" status today.
So what I need is a way to include a calculation in my current DAX, that counts the days since each serial went into "Redress In" status - Where there are no "Redress Out" after "Redress In" for the specific serial number.
Here is my current DAX:
DAYS ON REDRESS COL =
VAR myindex = CALCULATE(MAX (_ITH_All2[Index]),_ITH_All2[Location Status]="Redress Out")
VAR myserial = _ITH_All2[SERIAL_NO]
VAR previousindex =
CALCULATE (
MAX ( _ITH_All2[Index] ),_ITH_All2[Location Status]="Redress In",
FILTER ( _ITH_All2,_ITH_All2[SERIAL_NO] = myserial
&& _ITH_All2[Index] < myindex)
)
VAR previousdate =
CALCULATE (
MAX ( _ITH_All2[Date Applied] ),
FILTER (
_ITH_All2,
_ITH_All2[Index] = previousindex
&& _ITH_All2[SERIAL_NO] = myserial
)
)
RETURN
IF ( previousdate , _ITH_All2[Date Applied] - previousdate)
Which gives me this data, but as you see, this serial number is currently in "Redress In" status, so I would like to count the days from "Redress in" to TODAY.
Thanks in advance.
Solved! Go to Solution.
@Ken_Jorp , Create these three measures and try
_date =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[date applied] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( Min ('Table'[status] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[date applied] = __date )
_status =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[date applied] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( Min ('Table'[date applied] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[date applied] = __date )
diff = if([Status] ="Redress In", datediff(_date, today(), day), blank())
diff = sumx(values(Table[ID]),if([Status] ="Redress In", datediff(_date, today(), day), blank()))
@Ken_Jorp , Create these three measures and try
_date =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[date applied] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( Min ('Table'[status] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[date applied] = __date )
_status =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[date applied] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( Min ('Table'[date applied] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[date applied] = __date )
diff = if([Status] ="Redress In", datediff(_date, today(), day), blank())
diff = sumx(values(Table[ID]),if([Status] ="Redress In", datediff(_date, today(), day), blank()))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |