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 everyone,
Please can someone help me with the following. I tried to use the approach expalined in this link (https://powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbe...), but in vain.
I want to know if a given NCR was active or not based on the Raised Date range selected. For example, the NCR No 456 which was closed on 03/02/2020 is active, but if I change the range upto today, then it would say N.
Thanks Everyone, Habib
Solved! Go to Solution.
Hi @HabibAdil ,
Just try this:
NCR Active_YN =
VAR StatDate =
MINX ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] )
VAR EndDate =
MAXX ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] )
RETURN
IF (
SELECTEDVALUE ( OE_BD_NCR_Reports[NCR_Date_Raised] )
IN VALUES ( 'Calendar'[Date] ),
SWITCH (
TRUE (),
ISBLANK ( SELECTEDVALUE ( OE_BD_NCR_Reports[NCR_Date_Closed] ) ), "Y",
SELECTEDVALUE ( OE_BD_NCR_Reports[NCR_Date_Closed] ) > EndDate, "Y",
"N"
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @HabibAdil ,
Just try this:
NCR Active_YN =
VAR StatDate =
MINX ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] )
VAR EndDate =
MAXX ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] )
RETURN
IF (
SELECTEDVALUE ( OE_BD_NCR_Reports[NCR_Date_Raised] )
IN VALUES ( 'Calendar'[Date] ),
SWITCH (
TRUE (),
ISBLANK ( SELECTEDVALUE ( OE_BD_NCR_Reports[NCR_Date_Closed] ) ), "Y",
SELECTEDVALUE ( OE_BD_NCR_Reports[NCR_Date_Closed] ) > EndDate, "Y",
"N"
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@HabibAdil , Try like
measure =
var _minx = minx(allselcted(Date), Date[date])
var _maxx = minx(allselcted(Date), Date[date])
return
if( isblank(
calculate(countrows(Table), filter(Table,((isblank(Table[Date_closed]) && Table[start_date] <=_minx) || Table[Date_closed] >=_minx) && (isblank(Table[Date_closed]) || Table[Date_closed] >=_minx)))), "No", "Yes")
Hi@ Amitchandak,
Thank you for quick reply. I get No for all of them. The measure I create is as follow. I have a calender table which is related to NCR_Date_Raised field in OE_BD_NCR_Reports table. I do not see the var _max being used in the measure.
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |