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 ...
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

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
Icon for Super User rankSuper 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

  • neilmc's avatar
    neilmc
    Frequent Visitor
    3 years ago

    Sorry I was just putting it as a concept - I think this might explain it better rather than providing random tables (sorry again)

     

    IDStartEnd
    1Jan 23Feb 23
    2Jan 23Mar 23
    4Feb 23 
    3Jan 23Jan 23
    5Jan 23Feb 23

     

    Ongoing open cases totals:

    Jan 23 = 4

    Feb 23 = 4

    Mar 23 =  2

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      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

      • neilmc's avatar
        neilmc
        Frequent Visitor
        3 years ago

        Perfect thanks for your help much appreciated