cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Memorable Member
Memorable Member

Datediff - column but not measure

A table had 2 date fields.  I could Datediff them to create a calculated column.  But when trying a measure of a Table visual - the Datediff would not work.  I could not select the table/field sources - they weren't presented by the intellisense and if I free form typed them in anyway it did not work.

 

I am trying to get a conceptual handle on the reason for this - in the broad sense involving potentially other DAX functions.  To understand in advance, rather than trial & error if it can be used as a Measure or not.

 

 

 

 

www.CahabaData.com
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

12 REPLIES 12
Highlighted
Microsoft
Microsoft

What happens when you try to create a measure over the column you made using the DATEDIFF function?

 

eg, SUM of the new column


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted

Your reply has me a little perplexed.  Perhaps my post wasn't entirely clear.  The calculated column works fine and so I have no issue in using that in a visual. 

 

I don't need the calculation as a Measure - I am just perplexed as to why it can't be done using a Measure.  In fact I wasted a bit of time first attempting it as a Measure and so wondering & trying to better educate myself on when/why a Measure is not applicable for certain DAX functions and recognizing that fact up front rather than trial & error. 

 

www.CahabaData.com
Highlighted

Oh ok, sorry, I thought you were also trying to create a measure over the data. 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Community Champion
Community Champion


@CahabaData wrote:

Your reply has me a little perplexed.  Perhaps my post wasn't entirely clear.  The calculated column works fine and so I have no issue in using that in a visual. 

 

I don't need the calculation as a Measure - I am just perplexed as to why it can't be done using a Measure.  In fact I wasted a bit of time first attempting it as a Measure and so wondering & trying to better educate myself on when/why a Measure is not applicable for certain DAX functions and recognizing that fact up front rather than trial & error. 

 


 

Can you post some sample data?

 

Intellisense not allowing you to select the column does not indicate that it is not possible

rather that you need an aggregator such as MIN or MAX

Highlighted

hey Sean, thanks for the question..... in the table just 2 fields that are Date types; side by side - nothing unusal about them that I thought a sample would even be needed...

 

since the attempted Measure Field is to be Date Difference (in Days) is row by row - there would not be Min/Max involved....

 

works fine as a calculated column, just wondering why can't be a measure approach in a visual.....  purely theoretical educational effort.....

www.CahabaData.com
Highlighted
Community Champion
Community Champion

Highlighted

Thank you Sean.  Having to use the Min in a measure but not a column does have me a little baffled. 

 

Trying to widen my understanding of DAX in this situational context - and presume the logic of this example is true for other DAX functions.

 

 

www.CahabaData.com
Highlighted
Anonymous
Not applicable

Hi Sean,

 

I know it's been a while but thank you for posting a solution. I noticed that when I use the measure, all the zeroes get filtered out. In my situation a 0 means that the order was delivred on time. Is there anyway the measure can be edited to keep the 0's?

 

Thanks!

Highlighted
Community Champion
Community Champion

@Anonymous

Give this a try...

The "=" should take care of this...

 

Days Measure = 
SWITCH (
    TRUE (),
    MIN('Projects'[start_date]) <= MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[start_date]), MIN('Projects'[end_date]), DAY ),
    MIN('Projects'[start_date]) > MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[end_date]), MIN('Projects'[start_date]), DAY )* -1
)

Good Luck! Smiley Happy

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors