cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors