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: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mohassan99
Helper II
Helper II

Meas in tbl1 w only dates to get count in disconnected tbl where tbl1 date is between 2 tbl 2 dates

‎02-22-2023 04:21 PM

How do I calculate the number of rows on a particular date where the issue received date is in the past and the issue resolved date is in the future.

 

I found this on the PBI community page:
Count-Rows-Falling-between-two-dates-from-disconnected-table/ 


[I dont get it. What vis. The tables and columns dont seem independent of visualizations. Maybe if the calculation works, but the trend doesn't, but I'm not getting the right result from the measure.]

 

>But if you do want to have that one displayed without bringing
'Table 1'[Created] and
'Table 1'[Created +90]
then you need an index column and do this with a new measure


BETWEEN seems like what I need because we want rows in 'Table_owssvr_ioIntakesUnf' where 'IntakeVolume'[LastOfMonth] is between 'Table_owssvr_ioIntakesUnf'[Date Intake Request Received] and 'Table_owssvr_ioIntakesUnf'[Issue Resolved], in the 'IntakeVolume' table. I want to create a trend of intakes in process on the last day of each month by month.

I tried to copy the pattern in the following formula but it returns the size of the whole dataset without filters.

'Table_owssvr_ioIntakesUnf' is produced 'as of' a single date. It has dates like date received and resolved and more, but not an as of date because that's 'as of' data refresh.

 

One difference I see is where the columns are.

 

 

 

 

IntakeVolume =
  CALCULATE(
    COUNT('Table_owssvr_ioIntakesUnf'[ID]),
    DATESBETWEEN (
      'IntakeVolume'[LastOfMonth],
      MAX ( 'Table_owssvr_ioIntakesUnf'[Date Intake Request Received] ), 
      MAX ( 'Table_owssvr_ioIntakesUnf'[Issue Resolved] )
    )
  )

 

 

 

 


)

   
MonthLastOfMonthIntakeVolume
Tuesday, February 1, 20222/28/2022 0:002863

 

 

IDDate Intake Request ReceivedIssue Resolved
1777Monday, July 12, 2021Friday, September 17, 2021
1884Thursday, September 30, 2021Friday, October 22, 2021
1589Tuesday, April 6, 2021Thursday, April 22, 2021
1895Tuesday, October 5, 2021Tuesday, October 19, 2021
1117Tuesday, September 15, 2020Friday, September 25, 2020
1907Tuesday, October 12, 2021Friday, October 22, 2021
1855Thursday, August 26, 2021Friday, September 3, 2021
1718Wednesday, June 2, 2021Wednesday, June 9, 2021
1719Wednesday, June 2, 2021Wednesday, June 9, 2021
1954Wednesday, November 10, 2021Monday, November 15, 2021
1587Monday, April 5, 2021Wednesday, April 7, 2021
1860Tuesday, August 31, 2021Thursday, September 2, 2021
1981Tuesday, November 23, 2021Monday, November 29, 2021
1832Thursday, August 12, 2021Friday, August 13, 2021
1865Friday, September 10, 2021Monday, September 13, 2021
1882Tuesday, September 28, 2021Wednesday, September 29, 2021
1908Tuesday, October 12, 2021Tuesday, October 12, 2021
2026Friday, December 10, 2021Friday, December 10, 2021
1034Tuesday, August 25, 2020Tuesday, November 17, 2020
1442Thursday, January 21, 2021Wednesday, April 14, 2021
1371Thursday, December 10, 2020Friday, March 5, 2021
1195Monday, October 5, 2020Friday, December 18, 2020
1062Tuesday, September 1, 2020Tuesday, November 10, 2020
1065Tuesday, September 1, 2020Tuesday, November 10, 2020
1520Wednesday, March 3, 2021Monday, May 10, 2021
1322Thursday, November 19, 2020Tuesday, January 26, 2021
1297Friday, November 6, 2020Friday, January 8, 2021
1466Friday, February 5, 2021Wednesday, March 24, 2021
1029Tuesday, August 25, 2020Tuesday, October 6, 2020
1061Tuesday, September 1, 2020Tuesday, October 6, 2020
1069Wednesday, September 2, 2020Tuesday, October 6, 2020
1296Friday, November 6, 2020Friday, December 11, 2020
1294Friday, November 6, 2020Wednesday, December 9, 2020
1460Monday, February 1, 2021Tuesday, March 2, 2021
1093Thursday, September 10, 2020Wednesday, October 7, 2020
1476Tuesday, February 9, 2021Friday, March 5, 2021
1177Wednesday, September 30, 2020Friday, October 23, 2020
1183Thursday, October 1, 2020Wednesday, October 21, 2020
1292Friday, November 6, 2020Monday, November 30, 2020
1298Friday, November 6, 2020Monday, November 30, 2020
1238Wednesday, October 14, 2020Monday, November 2, 2020
1403Tuesday, January 5, 2021Monday, January 25, 2021
1437Tuesday, January 19, 2021Friday, February 5, 2021
1282Thursday, November 5, 2020Friday, November 20, 2020
1433Friday, January 15, 2021Tuesday, February 2, 2021
1072Thursday, September 3, 2020Friday, September 18, 2020
1429Thursday, January 14, 2021Friday, January 29, 2021
1025Monday, August 24, 2020Friday, September 4, 2020
1304Tuesday, November 10, 2020Friday, November 20, 2020
1059Tuesday, September 1, 2020Thursday, September 10, 2020
1432Friday, January 15, 2021Tuesday, January 26, 2021
1368Wednesday, December 9, 2020Tuesday, December 15, 2020
1068Wednesday, September 2, 2020Tuesday, September 8, 2020
1231Tuesday, October 13, 2020Friday, October 16, 2020
1232Tuesday, October 13, 2020Friday, October 16, 2020
1299Friday, November 6, 2020Wednesday, November 11, 2020
1517Tuesday, March 2, 2021Friday, March 5, 2021
1247Monday, October 19, 2020Wednesday, October 21, 2020
1277Tuesday, November 3, 2020Wednesday, November 4, 2020
1290Thursday, November 5, 2020Friday, November 6, 2020
1308Tuesday, November 10, 2020Wednesday, November 11, 2020
1075Friday, September 4, 2020Friday, September 4, 2020
1135Monday, September 21, 2020Monday, September 21, 2020
1194Friday, October 2, 2020Saturday, October 3, 2020
1314Friday, November 13, 2020Friday, November 13, 2020
1551Thursday, March 11, 2021Thursday, March 11, 2021
1970Thursday, November 18, 2021Thursday, December 9, 2021
866Monday, July 20, 2020Tuesday, July 21, 2020
1374Thursday, December 10, 2020Wednesday, July 7, 2021
1431Friday, January 15, 2021Wednesday, July 7, 2021
1758Tuesday, June 22, 2021Wednesday, October 13, 2021
1017Tuesday, August 18, 2020Tuesday, February 23, 2021
1293Friday, November 6, 2020Wednesday, March 17, 2021
1254Wednesday, October 21, 2020Thursday, February 4, 2021
1485Friday, February 12, 2021Monday, May 10, 2021
1858Monday, August 30, 2021Monday, August 30, 2021
1831Thursday, August 12, 2021Friday, October 29, 2021
1917Tuesday, October 19, 2021Wednesday, October 27, 2021
1964Tuesday, November 16, 2021Tuesday, November 23, 2021
1425Tuesday, January 12, 2021Friday, May 28, 2021
1478Tuesday, February 9, 2021Wednesday, May 19, 2021
1339Monday, November 30, 2020Tuesday, December 1, 2020
1421Monday, January 11, 2021Tuesday, February 23, 2021
1428Thursday, January 14, 2021Tuesday, February 23, 2021
1241Thursday, October 15, 2020Monday, October 19, 2020
1753Friday, June 18, 2021Friday, June 25, 2021
1994Wednesday, December 1, 2021Wednesday, December 1, 2021
1079Tuesday, September 8, 2020Wednesday, September 30, 2020
1788Monday, July 19, 2021Wednesday, September 8, 2021
1909Tuesday, October 12, 2021Thursday, October 28, 2021
1913Monday, October 18, 2021Tuesday, November 2, 2021
1949Monday, November 8, 2021Tuesday, November 23, 2021
1804Wednesday, July 21, 2021Friday, July 30, 2021
1741Monday, June 14, 2021Wednesday, June 16, 2021
123Friday, June 21, 2019Monday, June 24, 2019
1838Wednesday, August 18, 2021Thursday, August 19, 2021
1900Thursday, October 7, 2021Friday, October 8, 2021
1961Monday, November 15, 2021Tuesday, November 16, 2021
Labels:
Message 1 of 3
361 Views
0
2 REPLIES 2
mohassan99
Helper II
Helper II

‎02-23-2023 10:18 PM

Yes I am. Open issues as of Jan 21, Feb 28, ... Dec 31, but the source file is one row per issue, ie pk = id, so I had to create a file with a column jan 31, feb 28, ...dec 31, and cancluate end of month, then try to calculate count of rows where my date is between received and resolved dates. 

 

From link 4: this seems similar, but more complicated:

 

 

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

 

 

 So would my code look like this, because this gives me the error:

CROSSFILTER function can only use the two column references participating in a relationship.

 

If I remove the CROSSFILTER from the count I get 246 instead of the 46 I should get.

 

 

Issue Count = 
CALCULATE(
  COUNTx(
    FILTER(Table_owssvr_ioIntakeUnf,
      Table_owssvr_ioIntakeUnf[Received Date]<=max('IntakeVolume'[LastOfMonth]) 
      && (ISBLANK(Table_owssvr_ioIntakeUnf[Issue Resolved]) 
      || Table_owssvr_ioIntakeUnf[Issue Resolved] > max('IntakeVolume'[LastOfMonth]))), 
      (Table_owssvr_ioIntakeUnf[Id ])
    ),
    CROSSFILTER(
      Table_owssvr_ioIntakeUnf[Received Date],
        'IntakeVolume'[LastOfMonth],
         None
    )
  )

 

 

 

Message 3 of 3
283 Views
0
amitchandak
Super User
Super User

‎02-22-2023 08:50 PM

@mohassan99 , Are you looking for Open issues ?

 

A similar one can help

Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

 

or

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM



Message 2 of 3
300 Views
0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All