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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cshier
New Member

Comparing Date Counts

Hi,

I appreciate that this is a question that has been asked and answered many times before but despite my best efforts in looking in to date tables, I have not been able to wrap my head around it.

I'd like to compare two counts of dates along a timeline, a started date and and end date, while keeping date hierarchies available. 

cshier_2-1680201629479.png

 

cshier_1-1680201535704.png

Desired output: Jan should have 4 started, 2 ended, and Feb should have 3 started, 5 ended. 

It's very likely something simple but any help in connecting the dots will be appreciated, beginner PowerBI user here. 

 

Onedrive link to pbix file: https://capilanou1-my.sharepoint.com/:u:/g/personal/cshier_capilanou_ca/EUc08R9JKd1EqXxUXBLde9EBW8A7...

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

I wouldn't say it is simple but it is something to learn to understand relationships and DAX.

First you want the date table to filter the Fact table so change the relationship to one-way.

The important thing, though, is to activate the relationship when using the 'End' measure.  So you'll write an explicit measure to get the Count of end records while using the relationship which is inactive currently.  

It'll be a simple Calculate with count and the keyword USERELATIONSHIP.

Here's an example

https://www.learndax.com/power-bi-userelationship-function-with-example/ 

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

I wouldn't say it is simple but it is something to learn to understand relationships and DAX.

First you want the date table to filter the Fact table so change the relationship to one-way.

The important thing, though, is to activate the relationship when using the 'End' measure.  So you'll write an explicit measure to get the Count of end records while using the relationship which is inactive currently.  

It'll be a simple Calculate with count and the keyword USERELATIONSHIP.

Here's an example

https://www.learndax.com/power-bi-userelationship-function-with-example/ 

Beautiful! That was exactly the missing piece! Thank you for that. I was starting to lose my mind and you have restored my sanity for now.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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