cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bee_vite Frequent Visitor
Frequent Visitor

Re: Measure to Calculate the number of days between two date columns

First, thanks for this.. as close as I've come to solving the problem. However, it doesn't quite work as expected with my data and it may be that it has something to do with the two tables ('basic') and ('statuschanges') involved. The are related, but when I use this formula, the "Days Measure" column returns a constant 365 for all records:

 

Days Measure =

SWITCH (

   TRUE (),

   MIN('basic'[submissiondate]) < MIN('statuschanges'[statusdate]), DATEDIFF ( MIN('basic'[submissiondate]), MIN('statuschanges'[statusdate]), DAY ),

   MIN('Projects'[submissiondate]) > MIN('statuschanges'[statusdate]), DATEDIFF ( MIN('basic'[submissiondate]), MIN('statuschanges'[statusdate]), DAY )* -1)

 

at the bottom of the screen, I get the message "TABLE: basic (107,846 rows) COLUMN: Days Measure (1 distinct values)"

Any help would be appreciated!

Thanks ~bee

Highlighted
TonyO Frequent Visitor
Frequent Visitor

Re: Measure to Calculate the number of days between two date columns

re: "the "Days Measure" column returns a constant 365 for all records":

 

Double check the relationships and aggregated fields to make sure you're displaying the correct field. Often when a field exists in multiple linked tables it's possible to reference the wrong field, in which case you'll get the same value for all records.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 2,875 guests
Please welcome our newest community members: