Forum Discussion
Counting dates that fall between date range in another table (M to M relationship)
I have a flag or measure REMCUT which shows if an open date from one table occurs between a date range in another table for the same account. My issue is that I would like to count the number of service orders that occur in a given month with the REMCUT flag but my bar chart goes blank when I add it.
REMCUT =
CALCULATE(COUNT(RemoteCutoff[BI_SO_NBR]),
FILTER(RemoteCutoff,
RemoteCutoff[BI_ACCT] = SELECTEDVALUE(PPBilling[BI_ACCT])&&
RemoteCutoff[BI_OPEN_DT] >= SELECTEDVALUE(PPBilling[BI_BCH_DT])&&
RemoteCutoff[BI_OPEN_DT] <= SELECTEDVALUE(PPBilling[EndDate])&&
SELECTEDVALUE(PPBilling[BI_TRAN_ACTIVITY]) = "ADD"))
Below you can see the REMCUT flags 12 service orders for this account which occured between BI_BCH_DT and EndDate. In the table below that, service order 2676574 does not get flagged because it occurs outside of the range.
How can I get the count of service orders to show by the month they were opened?
The two tables are joined on a many to many relationship between accounts if that matters.
Below is a link to sample file
https://www.dropbox.com/s/odz4cw4dztov4nd/Page_Test.pbix?dl=0
Thank you for your help!
Hi akmiller ,
I'm sorry I don't understand your needs very clearly, is a given month given between two dates in another table or in some other way? Can you provide me with the expected results you want in the form of a table.
You can refer to the following documents that may be helpful to you:
Solved: Check if record from table A falls within a date r... - Microsoft Power BI Community
Solved: Count items in table A if filter date from table B... - Microsoft Power BI Community
Solved: DAX for counting overlapping date ranges across mu... - Microsoft Power BI Community
Solved: Count Rows Falling between two dates ( from discon... - Microsoft Power BI Community
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi akmiller ,
I'm sorry I don't understand your needs very clearly, is a given month given between two dates in another table or in some other way? Can you provide me with the expected results you want in the form of a table.
You can refer to the following documents that may be helpful to you:
Solved: Check if record from table A falls within a date r... - Microsoft Power BI Community
Solved: Count items in table A if filter date from table B... - Microsoft Power BI Community
Solved: DAX for counting overlapping date ranges across mu... - Microsoft Power BI Community
Solved: Count Rows Falling between two dates ( from discon... - Microsoft Power BI Community
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, the second link (https://community.powerbi.com/t5/Desktop/Count-items-in-table-A-if-filter-date-from-table-B-falls-between/m-p/1220695) and specifically this code:
Serial Number in TB 1 = VAR t = FILTER ( TableA, TableA[Start Date] <= MIN ( 'Created date from table B'[Created Date] ) && TableA[End Date] >= MAX ( 'Created date from table B'[Created Date] ) ) RETURN COUNTROWS ( FILTER ( t, [Serial Number] IN VALUES ( TableB[Serial Number] ) ) )Got me where I needed to be. Thanks for your help!
