Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Cuminoid
Frequent Visitor

Counting number of active agreements in the selected time period

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.

 

Cuminoid_0-1667539548858.png

 

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.

Cuminoid_1-1667539782257.png

 

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.

3 REPLIES 3
Cuminoid
Frequent Visitor

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.

Cuminoid_2-1667779089875.png

 

Cuminoid_0-1667778299724.png

 

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: 

 

Cuminoid_1-1667778642495.png

 

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 weekNumber of all agreementsNumber of current agreements
Friday, 04 November 202281632567
Friday, 28 October 202281622569
Friday, 21 October 202281622572
Etc.Etc.Etc.

 

Any further thoughts on this?

 

Thank you

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

TheoC
Super User
Super User

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:

TheoC_5-1667544405496.png

 

Output:

TheoC_4-1667544390610.png

 

For context, below are my tables:

 

TheoC_6-1667544461292.png

 

TheoC_7-1667544480459.png

 

Hope this helps!

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors