cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Community Champion
Community Champion

12 REPLIES 12
Anonymous
Not applicable

Why does no one explain solutions anymore?  Why can a measure not perforam a datediff when a calc column can?

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!

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
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

Thank you! I was having the same problem. Adding MIN fixed it.

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
Community Champion
Community Champion

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!

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

Anonymous
Not applicable

Sean,

 

It worked like a charm! Thank you very much.

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

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!

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors