Forum Discussion

jojo54's avatar
jojo54
Frequent Visitor
7 years ago
Solved

Start and end time

Hi there, pretty new to Power BI.  I'm trying to replicate something I do in Excel in Power BI, mainly because excel can't handle the amount of data that's being processed.   I'm trying to figure o...
Need Help
  • Geradav's avatar
    Geradav
    7 years ago

    Hi jojo54 

     

    See if the following can fix it for what you need

    Number of clients 2 =
VAR vMinVal =
    MIN ( TimeTable[Value] )
VAR vMaxVal =
    MAX ( TimeTable[Value] )
VAR vNumberOfClients =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( ClientsTbl ),
            ClientsTbl[Entry Time] <= vMinVal
                && ClientsTbl[Exit Time] >= vMaxVal
        )
    )
RETURN
    vNumberOfClients

     

    Whenever I have more time I could think of something better.

Geradav's avatar
Geradav
Icon for Responsive Resident rankResponsive Resident
7 years ago

Hi jojo54 

 

See if the following can fix it for what you need

Number of clients 2 =
VAR vMinVal =
    MIN ( TimeTable[Value] )
VAR vMaxVal =
    MAX ( TimeTable[Value] )
VAR vNumberOfClients =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( ClientsTbl ),
            ClientsTbl[Entry Time] <= vMinVal
                && ClientsTbl[Exit Time] >= vMaxVal
        )
    )
RETURN
    vNumberOfClients

 

Whenever I have more time I could think of something better.

kriswright77's avatar
kriswright77
New Member
5 years ago

Hi Geradev

 

I have a similar problem to that faced by the OP, and I have tried implementing your solutions for my own purpose, but I am coming up with incorrect results (probably similar to the OP)

I dont yet dabble in BI, but have recently started to teach myself Power Query & Power Pivot in Excel, which is similar, so hopefully not the reason for the results error.

If I create a Pivot Table measure similar to your 1st solution, some results are incorrect for a few periods and a few companies (I need to count number of people from a particular company at each period).
And if I try the 2nd solution the I get the correct Grant Total of people in each period, but it repeats for every company.

 

With regards the 1st solution, it seems that it is trying to relicate an Excel CountIfs function, where it takes the Minimum (by Company) of "People Entering Before the Time Period" & "People Leaving After the Time Period", irrespective of when they entered.

The result I think I actually need is the Count of People who have Entered and Not Left.

Is there a way of doing this?

 

With regards to the 2nd solution, I just havent learnt enough yet to work out how to adapt the formula so it returns by Company.

 

Any help you can give will be gratefully received.

 

Kris