Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Data Days is here! Join us now for 60+ days of learning, challenges, and connection. Learn more

Reply
luzrueda
Frequent Visitor

How to count open cases by selected date without generating all intermediate dates?

‎05-08-2026 01:55 AM

I’m trying to build a Power BI dashboard that shows how many cases were open on any given day/week, without generating a full list of dates between the Open Date and Close Date for every case.

Current approach

Right now, I generate all dates between:

  • Case Open Date
  • Case Close Date

This works, but the dataset becomes very large and refresh/update times are very slow.

What I want

I want to calculate dynamically which cases were open on a selected date (or during a selected week) using a measure, instead of creating all intermediate dates.

Example:

Case Open Date Close Date
A01.04.202522.05.2025

If the report is filtered to:

  • 01.05.2025 → Case A should be counted as Open
  • Any week between April and May 2025 → Case A should also appear as open

Questions

  1. Is it possible to do this efficiently with a DAX measure instead of generating all dates?
  2. What is the best practice for this scenario in Power BI?
  3. Which date table relationship should be used:
    • Open Date
    • Close Date
    • Or no active relationship at all?
  4. How would the DAX measure look for counting “cases open on selected date”?

I’m looking for a scalable solution because the dataset is large and performance is important. Thank you so much for your help

 

Caseopen DateClose Date
000046368102.12.201608.05.2026
000046368702.12.201608.05.2026
000057255716.12.201608.05.2026
000057255816.12.201608.05.2026
000057463019.12.201608.05.2026
000057537219.12.201608.05.2026
000064747822.12.201608.05.2026
000066113330.12.201608.05.2026
000082512430.01.201708.05.2026
000104791306.03.201708.05.2026
000113202428.03.201708.05.2026
000114076307.04.201708.05.2026
000139980707.06.201708.05.2026
000152294417.07.201708.05.2026
000152294517.07.201708.05.2026
000185704510.10.201708.05.2026
000185704710.10.201708.05.2026
000185714724.10.201708.05.2026
000185716826.10.201708.05.2026
000185717026.10.201708.05.2026
000185735909.11.201708.05.2026
000191340724.11.201708.05.2026
000197145028.11.201708.05.2026
000198403730.11.201708.05.2026
000198583606.12.201708.05.2026
000200306313.12.201708.05.2026
000210973918.12.201708.05.2026
000210995318.12.201708.05.2026
000214652122.12.201708.05.2026
000215456006.01.201808.05.2026
000229009717.01.201808.05.2026
000229274818.01.201808.05.2026
000230352804.02.201808.05.2026
000230760706.02.201808.05.2026
000262047814.03.201808.05.2026
000264242816.03.201808.05.2026
000264257016.03.201808.05.2026
000268014419.03.201808.05.2026
000269595522.03.201808.05.2026
000286603316.04.201808.05.2026
000294442118.04.201808.05.2026
000309491228.05.201808.05.2026
000309491328.05.201808.05.2026
000324407516.06.201808.05.2026
000325494305.07.201808.05.2026
000325495005.07.201808.05.2026
000325495305.07.201808.05.2026
000325496705.07.201808.05.2026
000325497105.07.201808.05.2026
000325497505.07.201808.05.2026
000325497605.07.201808.05.2026
000325499105.07.201808.05.2026
000325500605.07.201808.05.2026
000325502305.07.201808.05.2026
000325502505.07.201808.05.2026
000325678214.07.201808.05.2026
000325678914.07.201808.05.2026
000325680916.07.201808.05.2026
000325682516.07.201808.05.2026
000325682816.07.201808.05.2026
000325683016.07.201808.05.2026
000325683716.07.201808.05.2026
000325684416.07.201808.05.2026
000325687817.07.201808.05.2026
000325688317.07.201808.05.2026
000325688417.07.201808.05.2026
000325689917.07.201808.05.2026
000325690417.07.201808.05.2026
000325692317.07.201808.05.2026
000325695317.07.201808.05.2026
000325695417.07.201808.05.2026
000325697018.07.201808.05.2026
000326504620.07.201808.05.2026
000326506321.07.201808.05.2026
000337599224.07.201808.05.2026
000339634326.07.201808.05.2026
000339634926.07.201808.05.2026
000339635026.07.201808.05.2026
000339635126.07.201808.05.2026
000339635226.07.201808.05.2026
000339657927.07.201808.05.2026
000339658127.07.201808.05.2026
000339659227.07.201808.05.2026
000339660227.07.201808.05.2026
000339660827.07.201808.05.2026
000339661027.07.201808.05.2026
000339661227.07.201808.05.2026
000339664628.07.201808.05.2026
000339667031.07.201808.05.2026
000343265801.08.201808.05.2026
000343266001.08.201808.05.2026
000343282701.08.201808.05.2026
000344667301.08.201808.05.2026
000344668602.08.201808.05.2026
000345194004.08.201808.05.2026
000345214503.08.201808.05.2026
000345215004.08.201808.05.2026
000345227706.08.201808.05.2026
000345227806.08.201808.05.2026
000345227906.08.201808.05.2026
Labels:
Message 1 of 7
777 Views
2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

‎05-08-2026 07:10 AM

Make sure there is no relationship between your date table and your cases table, then write a measure like

Num open cases =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( Cases ),
        Cases[Open Date] <= MaxDate
            && (
                ISBLANK ( Cases[Close Date] ) || Cases[CloseDate] >= MaxDate
            )
    )
RETURN
    Result

This assumes that there is 1 row per case in your Cases table. If that's not the case then you will need to do a DISTINCTCOUNT on Case ID, but that could be significantly slower than a COUNTROWS.

The measure should work at any granularity of date. It counts the number of cases where the open date is on or before the end of the period and the close date is either blank ( presumably still open ) or is on or after the end of the period.

View solution in original post

Message 2 of 7
764 Views
cengizhanarslan
Super User
Super User

‎05-08-2026 12:19 PM

Use no active relationship between the Date table and the Cases table. Then use the following measure:

Open Cases =
VAR _SelectedStart =
    MIN ( 'Date'[Date] )
VAR _SelectedEnd =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Cases ),
        FILTER (
            ALL ( Cases ),
            Cases[Open Date] <= _SelectedEnd
                && (
                    ISBLANK ( Cases[Close Date] )
                        || Cases[Close Date] >= _SelectedStart
                )
        )
    )
_________________________________________________________
If this helped, ✓ Mark as Solution | Kudos appreciated
Connect on LinkedIn | Follow on Medium
AI-assisted tools are used solely for wording support. All conclusions are independently reviewed.

View solution in original post

Message 4 of 7
743 Views
6 REPLIES 6
v-echaithra
Community Support
Community Support

‎05-12-2026 03:48 AM

Hi @luzrueda ,

Thank you @cengizhanarslan , @johnt75  for your inputs.
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you.

Message 6 of 7
665 Views
luzrueda
Frequent Visitor
In response to v-echaithra

‎05-12-2026 04:50 AM

Thank you for your reminder and support!

Message 7 of 7
654 Views
0
cengizhanarslan
Super User
Super User

‎05-08-2026 12:19 PM

Use no active relationship between the Date table and the Cases table. Then use the following measure:

Open Cases =
VAR _SelectedStart =
    MIN ( 'Date'[Date] )
VAR _SelectedEnd =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Cases ),
        FILTER (
            ALL ( Cases ),
            Cases[Open Date] <= _SelectedEnd
                && (
                    ISBLANK ( Cases[Close Date] )
                        || Cases[Close Date] >= _SelectedStart
                )
        )
    )
_________________________________________________________
If this helped, ✓ Mark as Solution | Kudos appreciated
Connect on LinkedIn | Follow on Medium
AI-assisted tools are used solely for wording support. All conclusions are independently reviewed.
Message 4 of 7
744 Views
luzrueda
Frequent Visitor
In response to cengizhanarslan

‎05-12-2026 04:49 AM

Thank you so much @cengizhanarslan I got great results with your approach!

Message 5 of 7
654 Views
johnt75
Super User
Super User

‎05-08-2026 07:10 AM

Make sure there is no relationship between your date table and your cases table, then write a measure like

Num open cases =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( Cases ),
        Cases[Open Date] <= MaxDate
            && (
                ISBLANK ( Cases[Close Date] ) || Cases[CloseDate] >= MaxDate
            )
    )
RETURN
    Result

This assumes that there is 1 row per case in your Cases table. If that's not the case then you will need to do a DISTINCTCOUNT on Case ID, but that could be significantly slower than a COUNTROWS.

The measure should work at any granularity of date. It counts the number of cases where the open date is on or before the end of the period and the close date is either blank ( presumably still open ) or is on or after the end of the period.

Message 2 of 7
765 Views
luzrueda
Frequent Visitor
In response to johnt75

‎05-12-2026 04:48 AM

Thank you so much @johnt75 it was very helpful. I could definitely set this rules for my Dashboard

Message 3 of 7
654 Views
0

Helpful resources

Announcements
Fabric Data Days is here Carousel

Fabric Data Days 2026

Don't miss out on Data Days, June 15 through August 7. Learn Fabric, Power BI, SQL, AI and more.

May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All