cancel
Showing results for
Did you mean:
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
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])`

6 REPLIES 6
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:

.

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

Can you help?

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])`

Regular Visitor

## Re: DATEDIFF HELP

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

Anonymous
Not applicable

## Re: DATEDIFF HELP

Worked very well. Thanks .

Can you help me exclude weekends and holidays now ?

Thanks .

Highlighted
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

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 357 members 3,631 guests
Recent signins: