Forum Discussion

sagar512's avatar
sagar512
Helper I
4 years ago
Solved

Count Rows Falling between two dates ( from disconnected table )

Hi,

I am having two disconnected tables ( Task and Ticket).

On Task Table I need to count tickets (from the Ticket table) that fall between the task created date and Created +90 date.

 

In Excel, I used the below formula to get the expected output.

 

how can calculate the same in dax?

 

 

Task Table   
Task NumberCategoryCreatedCreated +90
Task 1A1-Jan-211-Apr-21
Task 2A1-May-2130-Jul-21
Task 3B1-Sep-2130-Nov-21
Task 4B1-Jun-2130-Aug-21
Task 5B1-Dec-211-Mar-22

 

Ticket Table
Ticket NumberCreated
Ticket 17-Jan-21
Ticket 22-Feb-21
Ticket 32-Feb-21
Ticket 46-Jun-21
Ticket 510-Jun-21
Ticket 619-Aug-21
Ticket 79-Sep-21
Ticket 813-Jun-21
Ticket 93-Mar-21
Ticket 109-Sep-21
Need Help DAX measure
  • smpa01's avatar
    smpa01
    4 years ago

      I still don't see an issue here 

     You need to display 

    'Table 1'[Created] and 
    'Table 1'[Created +90] in the viz for the DAX to evaluate in the filter context. Without having that axis present, the expression will not evaluate.
     
     
    But if you do want to have that one displayed without bringing
    'Table 1'[Created] and 
    'Table 1'[Created +90]
    then you need an index column and do this with a new measure

     

15 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    Here is one way to do this:

    Dax (calculated column):

    Tickets = calculate(COUNT(Tickets[TN]),DATESBETWEEN('Calendar'[Date],'Task Table'[Created],'Task Table'[C90]))

    Data model:

     

    End result:

    I hope this helps and if it does consider accepting this as a solution and giving a thumbs up!

    • sagar512's avatar
      sagar512
      Helper I
      4 years ago

      Thanks for the response; but unfortunately I cannot use the calculate function in the column, as I am working with powerbi data set (direct query). 

      Any way to do this in measure?

      • ValtteriN's avatar
        ValtteriN
        Community Champion
        4 years ago

        Hi,

        It works almost exactly the same with a measure: 

        tickets_ =
        var _sdate = max('Task Table'[Created])
        var _edate = max('Task Table'[C90]) return


        calculate(COUNT(Tickets[TN]),DATESBETWEEN('Calendar'[Date],_sdate,_edate))

         

    • sagar512's avatar
      sagar512
      Helper I
      4 years ago

      For some reason I don't see a way to attach powerbi file; so added my tables to the original post.

      will this help?

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    sagar512  try this measure

    Measure =
CALCULATE (
    COUNT ( 'Table 2'[Ticket Number] ),
    DATESBETWEEN (
        'Table 2'[Created],
        MAX ( 'Table 1'[Created] ),
        MAX ( 'Table 1'[Created +90] )
    )
)

     

     

    • sagar512's avatar
      sagar512
      Helper I
      4 years ago

      I added a category column into my data and this measure needs the lowest level (task number) to work.

      it's not working as expected if I remove the task number.

       

      the calculation should always work on task level and then aggregate per report context 

       

      Any way we can achieve this?