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

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.