cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DATEDIFF HELP

Combo selection column is: Date Demande
 
Quick question, if [Date de présentation à l'entité] is BLANK, I want to DATEDIFF between the Date Demande and the end of the month( Selected in the Date Demande combo)
 
Eg of one row im my table:
REF               Date demande     Date de présentation à l'entité
FP#00106     27/02/2019
 

My expression: 

M_Date_DIFF =
IF(t_Missions[Date de présentation à l'entité] = BLANK ();
DATEDIFF(t_Missions[Date demande]; EOMONTH(MAX(t_Missions[Date demande]);0); DAY);
DATEDIFF(t_Missions[Date demande]; t_Missions[Date de présentation à l'entité]; DAY)
)
1 ACCEPTED SOLUTION

Accepted Solutions
autodafe Regular Visitor
Regular Visitor

Re: DATEDIFF HELP

I Think you need MEASURES, not COLUMNS.

I have tried to replicate your scenario using data1 and data2 as dates, calculate 2 differente DATEDIFF and then a "FINAL" measure with an IF. Not super-elegant but it seems to be working

www.autodafe.net/tmp/EXAMPLE.pbix

1st I Calculate (Measure) End of Selected Month:

EOM = EOMONTH(SELECTEDVALUE(Cal[Date]);0)
 
then I have 2 differente DATEDIFFs
DDIFF1 =DATEDIFF(SELECTEDVALUE(Foglio1[data1]);[EOM];DAY)
DDIFF2 = DATEDIFF(SELECTEDVALUE(Foglio1[data1]);SELECTEDVALUE(Foglio1[data2]);DAY)
 
 
Then a simple IF takes what's needed:
Final = IF(ISBLANK([DDIFF2]); [DDIFF1];[DDIFF2])





View solution in original post

6 REPLIES 6
autodafe Regular Visitor
Regular Visitor

Re: DATEDIFF HELP

so the question is???
your formula looks correct. Are you creating a new COLUMN or new MEASURE ??

Anonymous
Not applicable

Re: DATEDIFF HELP

Hi @autodafe , 

 

It's a column. 

Here's the result: 

Capture.JPG.

 

as you can see it does not calculate until the end of the selected month (Date_Demande) - FP#00057

Can you help?

autodafe Regular Visitor
Regular Visitor

Re: DATEDIFF HELP

I Think you need MEASURES, not COLUMNS.

I have tried to replicate your scenario using data1 and data2 as dates, calculate 2 differente DATEDIFF and then a "FINAL" measure with an IF. Not super-elegant but it seems to be working

www.autodafe.net/tmp/EXAMPLE.pbix

1st I Calculate (Measure) End of Selected Month:

EOM = EOMONTH(SELECTEDVALUE(Cal[Date]);0)
 
then I have 2 differente DATEDIFFs
DDIFF1 =DATEDIFF(SELECTEDVALUE(Foglio1[data1]);[EOM];DAY)
DDIFF2 = DATEDIFF(SELECTEDVALUE(Foglio1[data1]);SELECTEDVALUE(Foglio1[data2]);DAY)
 
 
Then a simple IF takes what's needed:
Final = IF(ISBLANK([DDIFF2]); [DDIFF1];[DDIFF2])





View solution in original post

autodafe Regular Visitor
Regular Visitor

Re: DATEDIFF HELP

Here's what I got for a specific date (18/08/2018)

 

example.png

Anonymous
Not applicable

Re: DATEDIFF HELP

Worked very well. Thanks .

 

Can you help me exclude weekends and holidays now ? 

Thanks .

 

Highlighted
autodafe Regular Visitor
Regular Visitor

Re: DATEDIFF HELP

try to create a separate Date dimension, add one ro more attribute (true/false) for holidays and weekends, somethin like

Date                       Weekend                      Holiday
01/05/2019                 False                        True

02/05/2019                 False                        False

03/05/2019                 False                        False

04/05/2019                 True                         False

05/05/2019                 True                         False


the Weekend column could be calculated using the WEEKDAY function (if weekday is Sat or Sun  then  TRUE)
https://docs.microsoft.com/en-us/dax/weekday-function-dax

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 357 members 3,631 guests
Please welcome our newest community members: