Forum Discussion

HabibAdil's avatar
HabibAdil
Helper IV
5 years ago
Solved

Counting Active Rows in a Time Period

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-fro...
Need Help
Tips and Tricks
  • Icey's avatar
    Icey
    5 years ago

    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.

amitchandak's avatar
amitchandak
Super User
5 years ago

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

  • HabibAdil's avatar
    HabibAdil
    Helper IV
    5 years ago

    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.

     

    NCR Active_YN =
    var _minx = MINX(ALLSELECTED(Calender),Calender[Date])
    var _maxx = MAXX(ALLSELECTED(Calender),Calender[Date])
    return
    IF(ISBLANK(CALCULATE(COUNTROWS(OE_BD_NCR_Reports), FILTER(OE_BD_NCR_Reports,((ISBLANK(OE_BD_NCR_Reports[NCR_Date_Closed]) && OE_BD_NCR_Reports[NCR_Date_Raised]<=_minx) || OE_BD_NCR_Reports[NCR_Date_Closed]>= _minx && (ISBLANK(OE_BD_NCR_Reports[NCR_Date_Closed])||OE_BD_NCR_Reports[NCR_Date_Closed]>=_minx))))),"N","Y")