Calculate number of Visits Per Day

[Collins26390]

Hello,

I am trying to create a visual to demonstrate the number of visits that my engineers have completed over time. This should allow me to add slicers to do some analysis based on visits per location, per engineer etc. I have added a RealTimeData table from a streaming dataset to my Power BI report, which contains details of the location, engineer, date/time of last visit etc.

I have experimented with various DAX expressions to show how the visit numbers fluctuate over time, but so far I have been unsuccessful. 

Based on the data above, what I want to show is that there were 10 visits on 22/09/2023, 1 on 25/09/2023, 18 on 26/09/2023 and so on.

Any assistance would be greatly appreciated!

[Dangar332]

Hi, @Collins26390 

try below code
just adjust your table and column name

result =
  SUMX(
     FILTER(
        'Tablename',
         'Tablename'[date]=MAX('Tablename'[date])
     ),
     'Tablename'[visits]
   )

[Collins26390]

Hi @Dangar332 ,

Thank you for coming back to me. I've tried implementing your solution, but I'm a little confused about which column 'Tablename'[visits] should correspond to. PSB a sample of the table I'm working on:

I've tried using [Last Visit] with your formula, but it just seems to be replicating the information that is already in that column.

Thanks

[Dangar332]

Hi, @Collins26390 
try below

result =
calculate(
    sum('Tablename'[visits]), 
    'Tablename'[last visit]=MAX('Tablename'[last visit]),
    allexcept('tablename','tablename'[last visit])
  )
  

[Collins26390]

@Dangar332 ,

Sorry, that doesn't work. I don't have a 'visits' column, only the ones listed above.

[Dangar332]

HI, @Collins26390 

try below

visit count=
  calculate(
      count('tablename'[last visit]),
      allexcept('tablename','tablename'[last visit])
   )