Forum Discussion

Memphis28's avatar
Memphis28
Frequent Visitor
2 years ago
Solved

Count entries after a date

Good afternoon

I am looking for a little guidance on something and fingers crossed someone can support.

I have data that contains a scheduled delivery date, the data also shows the  dates the customer has called in to speak to us. All i am trying to do is write a measure that will count all the dates of all calls made after the delivery date. 

You will have to excuse the use of excel, over powerbi, it is the only way i could quickly hide the customer details.

I have tried using an IF function with a > than but it always pulls all the calls and not just the data specific to each account so rather than 6 (from the example below) i am getting almost 4000.

So for example account 7401 had a delivery date on the 18/03 and has made 6 calls after this date. See data below:
End Result wanted:

 

Raw data


Hope this makes sense and thank you in advance.

M/28

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Memphis28 ,

    I create a table as you mentioned.

    Then I create a measure and here is the DAX code.

    Calls made after date =
VAR _currentAN =
    SELECTEDVALUE ( 'Table'[Account Number] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Account Number] = _currentAN )
    )

    Finally you will get what you want.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Memphis28's avatar
    Memphis28
    Frequent Visitor
    2 years ago

    What may be of value but i am unsure is that the calls all have a qunique call reference ID in a seperate column if that makes it easier to count the individual calls coming in.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Memphis28 ,

    I create a table as you mentioned.

    Then I create a measure and here is the DAX code.

    Calls made after date =
VAR _currentAN =
    SELECTEDVALUE ( 'Table'[Account Number] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Account Number] = _currentAN )
    )

    Finally you will get what you want.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Write this measure

    Measure = countrows(filter(Data,Data[Call date]>Data[Delivery date]))

    To your table visual, drag Account Number and this measure.

    • Memphis28's avatar
      Memphis28
      Frequent Visitor
      2 years ago

      Thank you for the help. I am still getting the same issue where it isn't referencing the correct delivery date based on the account. I get the following error:

      If i enter Min or MAX to try and get around this error i get the same issue as before where it is adding all calls together and isn't dynamic based on the account. 
      The data is coming from 3 different tables, all have relationships set up (correctly has far as i can tell) but could this be causing this issue?

      Thanks in advance

      M/28