Counting number of active agreements in the selected time period

[Cuminoid]

Hello Power BI forum,

I am quite the novice in Power BI and I suspect I am now encountering a fairly typical issue. Here it goes.

I tried to simplify my dataset and relationships for the sake of this post. I wanted to attach the PBIX file but I couldn't find a way to remove the sensitive data from the PBIX (when switched to the Power Query view, all the data becomes available), so I will try to get away with screenshots initially.

--------

I have two data tables. One for agreements and one for cash collected for those agreements. I've also created a "Dates" table. Below is how the three are connected to each other.

When I display the data in tables, you will notice in the lower table that "number of agreements" and "number of current agreements" are repeating between weeks. Clearly, that is not accurate.

I added a couple cards to the visualisation and selected a particular date which gave me the correct numbers (I think... Not sure if I got the "Count of agreement ID" right yet).

To count the "Current" agreement IDs, I created a few measures. See below for the key ones (happy to add them all if it helps).

I think it works accurately (see the top table for its outcome). I know B11783 looks wrong, but it is not. Even though the date selected is showing Friday, the 28th, it is filtering for the entire week ending on Sunday, the 30th. It is a visualisation preference. (Don't ask. 🙂 )

Number of Current Agreements = CALCULATE(COUNT(Agreements[Agreement ID]),FILTER(Agreements,[Agreement Status]="Current"))

Agreement Status = 
    IF([Agreement Start Date] = BLANK(),"", 
		IF([Agreement Started?] = FALSE(), "Not Started",
			IF([Agreement End Date]=BLANK(),"Current",
				IF([Agreement ended prior to selected dates?] = TRUE(), "Expired", 
					IF([Agreement Still Active?] = TRUE(), "Current", "Expired")
				)  
			)
		)
	)	

What I am trying to do is for the lower table to show the correct number of agreements and current agreements in each row for the corresponding time period, which may be days, weeks, fortnights, months, quarters or years.

I tried to do it via CROSSFILTER function but haven't been able to succeed yet. 

I also created a new query via Power Query and repeated the entire agreement table for each day since 01/01/1970. It created a new table with more than 180,000,000 records and proven impractical. (Didn't solve my problem either.)

I thank you all in advance for your help.

[Cuminoid]

Thank you for your response and the effort you put into it, @TheoC. It is greatly appreciated.

For some reason those Agreement IDs always felt like they should be linked to each other. I never even considered removing that link. 

I followed your advice. Please see the most recent output below.

There are 2 unintended outcomes with the new connection layout.

1. I lost the ability to use a date slicer:

When I select a sub-set of a time period (I.e., report for the last quarter or simply select a week from the bottom table), the agreement status value disappears for agreements that do not fall into that period.

See below the tables when it is filtered for this fiscal year. Notice how the agreement status is blank now for all the old records, which would mess with the count of all agreements anytime a time filter is applied: 

2. The count of agreements (current and total) is not showing the totals anymore, but only showing the ones that have been started in the given week. I'd like it to show something like (I can see how my original description could be misleading. I apologise for that):

Last day of the week	Number of all agreements	Number of current agreements
Friday, 04 November 2022	8163	2567
Friday, 28 October 2022	8162	2569
Friday, 21 October 2022	8162	2572
Etc.	Etc.	Etc.

Any further thoughts on this?

Thank you

[Cuminoid]

Any other thoughts? Any help will be greatly appreciated. Thank you.

[TheoC]

Hi @Cuminoid 

I believe this is being caused as a result of the relationships in your model.  Is there a need to have the relationship between the Agreements table and Cash Collected table?  If you delete that relationship, you should find that your desired output will be as you require.

Model:

Output:

For context, below are my tables:

Hope this helps!