topic Re: How to count open cases by selected date without generating all intermediate dates? in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5181483#M188055 <P>Thank you for your reminder and support!</P> Tue, 12 May 2026 11:50:20 GMT luzrueda 2026-05-12T11:50:20Z How to count open cases by selected date without generating all intermediate dates? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5179727#M188007 <P>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.</P><H3>Current approach</H3><P>Right now, I generate all dates between:</P><UL><LI><STRONG>Case Open Date</STRONG></LI><LI><STRONG>Case Close Date</STRONG></LI></UL><P>This works, but the dataset becomes very large and refresh/update times are very slow.</P><H3>What I want</H3><P>I want to calculate dynamically which cases were open on a selected date (or during a selected week) using a <STRONG>measure</STRONG>, instead of creating all intermediate dates.</P><P>Example:</P><DIV class=""><DIV class="">Case Open Date Close Date <TABLE><TBODY><TR><TD>A</TD><TD>01.04.2025</TD><TD>22.05.2025</TD></TR></TBODY></TABLE></DIV></DIV><P>If the report is filtered to:</P><UL><LI><STRONG>01.05.2025</STRONG> → Case A should be counted as <STRONG>Open</STRONG></LI><LI>Any week between April and May 2025 → Case A should also appear as open</LI></UL><H3>Questions</H3><OL><LI>Is it possible to do this efficiently with a DAX measure instead of generating all dates?</LI><LI>What is the best practice for this scenario in Power BI?</LI><LI>Which date table relationship should be used:<UL><LI>Open Date</LI><LI>Close Date</LI><LI>Or no active relationship at all?</LI></UL></LI><LI>How would the DAX measure look for counting “cases open on selected date”?</LI></OL><P>I’m looking for a scalable solution because the dataset is large and performance is important. Thank you so much for your help</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Case</TD><TD>open Date</TD><TD>Close Date</TD></TR><TR><TD>0000463681</TD><TD>02.12.2016</TD><TD>08.05.2026</TD></TR><TR><TD>0000463687</TD><TD>02.12.2016</TD><TD>08.05.2026</TD></TR><TR><TD>0000572557</TD><TD>16.12.2016</TD><TD>08.05.2026</TD></TR><TR><TD>0000572558</TD><TD>16.12.2016</TD><TD>08.05.2026</TD></TR><TR><TD>0000574630</TD><TD>19.12.2016</TD><TD>08.05.2026</TD></TR><TR><TD>0000575372</TD><TD>19.12.2016</TD><TD>08.05.2026</TD></TR><TR><TD>0000647478</TD><TD>22.12.2016</TD><TD>08.05.2026</TD></TR><TR><TD>0000661133</TD><TD>30.12.2016</TD><TD>08.05.2026</TD></TR><TR><TD>0000825124</TD><TD>30.01.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001047913</TD><TD>06.03.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001132024</TD><TD>28.03.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001140763</TD><TD>07.04.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001399807</TD><TD>07.06.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001522944</TD><TD>17.07.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001522945</TD><TD>17.07.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001857045</TD><TD>10.10.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001857047</TD><TD>10.10.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001857147</TD><TD>24.10.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001857168</TD><TD>26.10.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001857170</TD><TD>26.10.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001857359</TD><TD>09.11.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001913407</TD><TD>24.11.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001971450</TD><TD>28.11.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001984037</TD><TD>30.11.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0001985836</TD><TD>06.12.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0002003063</TD><TD>13.12.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0002109739</TD><TD>18.12.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0002109953</TD><TD>18.12.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0002146521</TD><TD>22.12.2017</TD><TD>08.05.2026</TD></TR><TR><TD>0002154560</TD><TD>06.01.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002290097</TD><TD>17.01.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002292748</TD><TD>18.01.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002303528</TD><TD>04.02.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002307607</TD><TD>06.02.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002620478</TD><TD>14.03.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002642428</TD><TD>16.03.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002642570</TD><TD>16.03.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002680144</TD><TD>19.03.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002695955</TD><TD>22.03.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002866033</TD><TD>16.04.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0002944421</TD><TD>18.04.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003094912</TD><TD>28.05.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003094913</TD><TD>28.05.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003244075</TD><TD>16.06.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003254943</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003254950</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003254953</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003254967</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003254971</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003254975</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003254976</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003254991</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003255006</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003255023</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003255025</TD><TD>05.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256782</TD><TD>14.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256789</TD><TD>14.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256809</TD><TD>16.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256825</TD><TD>16.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256828</TD><TD>16.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256830</TD><TD>16.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256837</TD><TD>16.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256844</TD><TD>16.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256878</TD><TD>17.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256883</TD><TD>17.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256884</TD><TD>17.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256899</TD><TD>17.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256904</TD><TD>17.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256923</TD><TD>17.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256953</TD><TD>17.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256954</TD><TD>17.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003256970</TD><TD>18.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003265046</TD><TD>20.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003265063</TD><TD>21.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003375992</TD><TD>24.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396343</TD><TD>26.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396349</TD><TD>26.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396350</TD><TD>26.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396351</TD><TD>26.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396352</TD><TD>26.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396579</TD><TD>27.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396581</TD><TD>27.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396592</TD><TD>27.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396602</TD><TD>27.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396608</TD><TD>27.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396610</TD><TD>27.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396612</TD><TD>27.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396646</TD><TD>28.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003396670</TD><TD>31.07.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003432658</TD><TD>01.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003432660</TD><TD>01.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003432827</TD><TD>01.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003446673</TD><TD>01.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003446686</TD><TD>02.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003451940</TD><TD>04.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003452145</TD><TD>03.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003452150</TD><TD>04.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003452277</TD><TD>06.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003452278</TD><TD>06.08.2018</TD><TD>08.05.2026</TD></TR><TR><TD>0003452279</TD><TD>06.08.2018</TD><TD>08.05.2026</TD></TR></TBODY></TABLE> Fri, 08 May 2026 08:55:48 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5179727#M188007 luzrueda 2026-05-08T08:55:48Z Re: How to count open cases by selected date without generating all intermediate dates? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5179894#M188015 <P>Make sure there is no relationship between your date table and your cases table, then write a measure like</P> <LI-CODE lang="markup">Num open cases = VAR MaxDate = MAX ( 'Date'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( Cases ), Cases[Open Date] &lt;= MaxDate &amp;&amp; ( ISBLANK ( Cases[Close Date] ) || Cases[CloseDate] &gt;= MaxDate ) ) RETURN Result </LI-CODE> <P>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.</P> <P>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.</P> Fri, 08 May 2026 14:10:44 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5179894#M188015 johnt75 2026-05-08T14:10:44Z Re: How to count open cases by selected date without generating all intermediate dates? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5180009#M188018 <P>Use no active relationship between the Date table and the Cases table. Then use the following measure:</P> <LI-CODE lang="markup">Open Cases = VAR _SelectedStart = MIN ( 'Date'[Date] ) VAR _SelectedEnd = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( Cases ), FILTER ( ALL ( Cases ), Cases[Open Date] &lt;= _SelectedEnd &amp;&amp; ( ISBLANK ( Cases[Close Date] ) || Cases[Close Date] &gt;= _SelectedStart ) ) )</LI-CODE> Fri, 08 May 2026 19:19:50 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5180009#M188018 cengizhanarslan 2026-05-08T19:19:50Z Re: How to count open cases by selected date without generating all intermediate dates? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5181456#M188052 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="477032" data-lia-user-login="luzrueda" class="lia-mention lia-mention-user">luzrueda</a>&nbsp;,<BR /><BR />Thank you&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="1292787" data-lia-user-login="cengizhanarslan" class="lia-mention lia-mention-user">cengizhanarslan</a>&nbsp;,&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="240987" data-lia-user-login="johnt75" class="lia-mention lia-mention-user">johnt75</a>&nbsp; for your inputs.<BR /><SPAN>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.</SPAN><BR /><BR /><SPAN>Thank you.</SPAN></P> Tue, 12 May 2026 10:48:22 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5181456#M188052 v-echaithra 2026-05-12T10:48:22Z Re: How to count open cases by selected date without generating all intermediate dates? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5181479#M188053 <P>Thank you so much&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="240987" data-lia-user-login="johnt75" class="lia-mention lia-mention-user">johnt75</a>&nbsp;it was very helpful. I could definitely set this rules for my Dashboard</P> Tue, 12 May 2026 11:48:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5181479#M188053 luzrueda 2026-05-12T11:48:01Z Re: How to count open cases by selected date without generating all intermediate dates? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5181481#M188054 <P>Thank you so much&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="1292787" data-lia-user-login="cengizhanarslan" class="lia-mention lia-mention-user">cengizhanarslan</a>&nbsp;I got great results with your approach!</P> Tue, 12 May 2026 11:49:15 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5181481#M188054 luzrueda 2026-05-12T11:49:15Z Re: How to count open cases by selected date without generating all intermediate dates? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5181483#M188055 <P>Thank you for your reminder and support!</P> Tue, 12 May 2026 11:50:20 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-count-open-cases-by-selected-date-without-generating-all/m-p/5181483#M188055 luzrueda 2026-05-12T11:50:20Z