Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
CahabaData
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
Sean
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?

Phil_Seamark
Employee
Employee

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

DATEDIFF.gif

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!

Sean
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.