Forum Discussion

neilmc's avatar
neilmc
Frequent Visitor
3 years ago
Solved

Monthly data on closed cases

Hello,

I have tried to look at the various ongoing total help but am struggling - any advice gratefully recieved.

 

I have client data with a start date and end date (I have a calendar table as a date table)

 

IdStartDateEndDate
125/05/2022 23:0023/10/2022 23:00
209/04/2019 23:0013/12/2021 00:00
311/05/2021 23:0020/01/2022 00:00
423/04/2023 23:0029/05/2023 23:00
514/04/2021 23:0010/08/2021 23:00

 

What I would like to be able to do is calculate an ongoing Total clients that are open in a month within a year - not a monthly total but a overall total in that calendar month - if that makes sense!

 

eg

Jan 2022 = 203 clients were open

Feb 2022 = 199 Clients were open

 

Jan 2023 = 250 clients were open

Feb 2023 = 249 clients were open

Any help gratefully recieved

 

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    neilmc 

    do not create the relationship between fact table and date table.

    pls try this

    Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[StartDate]<=max('Table 2'[Date])&&('Table'[EndDate]>=min('Table 2'[Date])||ISBLANK('Table'[EndDate]))))

    pls see the attachment below

7 Replies

    • neilmc's avatar
      neilmc
      Frequent Visitor
      3 years ago

      Sorry the second is what I want to achieve using the data from the first table. 

       

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        3 years ago

        could you pls provide the expected output based on the sample data? I don't know how to get 250 for Jan 2023