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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RvdHeijden
Post Prodigy
Post Prodigy

Need help with my formula

I want to calculate the difference between 2 dates if both colums have values otherwise it can remain BLANK.

i thought i had the correct formula but i get the error:

 

In DATEDIFF function, the start date cannot be greater than the end date

 

In my limited experience this normally means i have blank values but i thought i fixed that with this formula


What am i doing Wrong ?

 

DATEDIFF =
IF (
    Query1[DueDate] =BLANK();
    0;
    IF (Query1[Datum Ziektemelding] < Query1[DueDate];
        -1 * DATEDIFF ( Query1[DueDate]; Query1[Datum Ziektemelding]; DAY);
        DATEDIFF ( Query1[Datum Ziektemelding]; Query1[DueDate]; DAY)
    )
)

1 ACCEPTED SOLUTION

You won't use "Enter Data", you will use "New Table" on the Modeling tab.

 

Also, your localization settings may require dates in a different format (I was giving MM/DD/YYYY).  You can also use DATE(2014, 1, 1) and DATE(2019, 12, 31) in place of the quoted strings.

 

datetable.PNG

View solution in original post

14 REPLIES 14
dedelman_clng
Community Champion
Community Champion

I think you just have the if/true and if/false backwards:

DATEDIFF = 
IF (
    Query1[DueDate] =BLANK();
    0;
    IF (Query1[Datum Ziektemelding] < Query1[DueDate];
        -1 * DATEDIFF ( Query1[Datum Ziektemelding]; Query1[Datum DueDate]; DAY);
        DATEDIFF ( Query1[DueDate]; Query1[Datum Ziektemelding]; DAY)
    )
)

 

 

date.PNG

 

 

 

 


Hope this helps,

David

@dedelman_clng

I think we are almost there but now the result is a negative value such as -337 (days).

If someone calls in sick on april 1st and gets better on april 4th then the result should be 3 and not -3

 

The datecolums have date/time value for example 6-4-2016 22:00:00 and some coul be sick for 1 day and now the result is 0 but it should be 1 (day) so the formula should be something like if the result of the formula is 0 then +1 or something

 

How do u change the formula so that these 2 variable are added ?

 

 

For the 3 vs -3, just move the " - 1 * " to the if/false condition.

 

If you are wanting to deal with days and ignore the time, you should make sure your columns are date values only.  6-4-2016 22:00 is less than 1 day away from 7-4-2016 (I'm assuming you're using non-US notation).  Strip off the the time portion and you should get 1 as the value.

 

Hope this helps,

David

@dedelman_clng

 

David,

 

i changed the formula and the 'data type' of both colums and now it has a possitive value so that problem is solved.

But if a person has the same date for 'start' and 'end' then the result is stil 0 instead of 1

 

In excel the formula would be easy because you could say that IF the result is 0 then 1 otherwise it just returns the value of the formula

 

DATEDIFF =
IF (
    Query1[DueDate] =BLANK();
    BLANK();
    IF (Query1[Datum Ziektemelding] < Query1[DueDate];
        DATEDIFF ( Query1[Datum Ziektemelding]; Query1[DueDate]; DAY);
        -1 * DATEDIFF ( Query1[DueDate]; Query1[Datum Ziektemelding]; DAY)
    )
)

@RvdHeijden OK - I understand the situation now.  The formula in DAX would be very similar to that in Excel.  Just use another "IF" clause.

 

DD = 
IF (
    Tab2[DueDate] = BLANK(),
    BLANK(),
	IF (IF (Tab2[Ziek] < Tab2[DueDate],
        DATEDIFF ( Tab2[Ziek], Tab2[DueDate], DAY),
        -1 * DATEDIFF ( Tab2[DueDate], Tab2[Ziek], DAY)
    ) = 0, 1, IF (Tab2[Ziek] < Tab2[DueDate],
        DATEDIFF ( Tab2[Ziek], Tab2[DueDate], DAY),
        -1 * DATEDIFF ( Tab2[DueDate], Tab2[Ziek], DAY)
)))

 

You could also simplify it a bit using variable notation:

 

DD = 
var DIFF = 
IF (Tab2[Ziek] < Tab2[DueDate], DATEDIFF ( Tab2[Ziek], Tab2[DueDate], DAY), -1 * DATEDIFF ( Tab2[DueDate], Tab2[Ziek], DAY) )
RETURN IF ( Tab2[DueDate] = BLANK(), BLANK(), IF (DIFF = 0, 1, DIFF)
)

@dedelman_clng

That did the trick, thanks for your help

 

However i still have one question but im not sure it's possible

 

I have some people who called in sick and are still sick so they have a date in column 'Ziek' but no value in 'Duedate'.

Is is possible to change the formula in a way that IF the Duedate is 'Blank' that it uses the date of TODAY() and until 'Duedate' has a value it calculates using TODAY () ?

 

 

I think I understand what you're asking for additionally (which should be possible), but just so I'm clear can you give me a mock-up of what you expect the results to looks like (3 columns: Due Date, Zeik and Difference)

@dedelman_clng

Here is a example of what i'm trying to say

 

2017-03-29_0838.png

 

Furthermore i noticed that if someone is sick and he's still sick in the weekend those 2 days are calculated with it.

Is is possible to just calculate the workdays ?

OK, so if you create a Date table and add to the model, this is formula for designating if a date is a workday

 

Weekday =
IF (
    OR ( WEEKDAY ( 'Date'[Date] ) = 1, WEEKDAY ( 'Date'[Date] ) = 7 ),
    FALSE,
    TRUE
)

 

Then your DateDifference (DD2) would be

 

DD2 =
VAR DIFF =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        ALL ( 'Date' ),
        DATESBETWEEN (
            'Date'[Date],
            Tab2[Ziek],
            IF ( ISBLANK ( Tab2[DueDate] ), TODAY (), Tab2[DueDate] )
        ),
        'Date'[Weekday] = TRUE ()
    )
RETURN
    IF ( DIFF = 1, 1, DIFF - 1 )

 

(DATESBETWEEN returns a table inclusive of the begin and end dates, so COUNTROWS(DATESBETWEEN(Date[Date], TODAY(), TODAY())) = 1, hence the need for subtracting 1 in the final formula).

 

Hope this helps

David

@dedelman_clng

 

The first part of your formula works like a charm but im not understanding the formula with the WEEKDAY

 

you want me to make a new table (via Enter Data ?) but if i copy your fomula in a new colum it gives a Syntax error.

Besides im not fully understandig this because if i get a blank table and copy this formula in it what does it use as reference ?

 

What data does 'Date'[Date] bring back because that is not a column in my various tables ?

Or is this a very stupid question ? 🙂 🙂

 

Weekday =
IF (
    OR ( WEEKDAY ( 'Date'[Date] ) = 1, WEEKDAY ( 'Date'[Date] ) = 7 ),
    FALSE,
    TRUE
)

You need to create a date table using the CALENDAR() function. 

 

DateTab = CALENDAR("1/1/2014", "12/31/2019")

 creates a table with one row for each date between 1/1/14 and 12/31/19

 

 

You can also do it in the Query Editor if you know Power Query (or find the code somewhere in the forums).

 

Once you have the date table created, join either DueDate or Zeik to it in the model, then you can use the calculated column code I mentioned previously.

@dedelman_clng

When i use hte function 'Enter Data' where do i fill in the formula ? it doesn't seem to recognize the CALENDAR function


DateTab = CALENDAR("1/1/2014", "12/31/2019")

You won't use "Enter Data", you will use "New Table" on the Modeling tab.

 

Also, your localization settings may require dates in a different format (I was giving MM/DD/YYYY).  You can also use DATE(2014, 1, 1) and DATE(2019, 12, 31) in place of the quoted strings.

 

datetable.PNG

To use TODAY() as a default when DueDate is blank:

 

DD = 
var DIFF = IF (Tab2[Ziek] < Tab2[DueDate],
        DATEDIFF ( Tab2[Ziek], Tab2[DueDate], DAY),
        -1 * DATEDIFF ( Tab2[DueDate], Tab2[Ziek], DAY)
    ) 
RETURN 
IF (
    Tab2[DueDate] = BLANK(),
    DATEDIFF ( Tab2[Ziek], TODAY(), DAY),
	IF (DIFF = 0, 1, DIFF)
)

 

In order to make use of calculating or not calculating Weekends, you will need to add a date table to your model and then all of the formulas will have to change to reference the date table.  I don't think DATEDIFF will work in that case - it will get more complex.  I will work on it, and in the meantime maybe someone else from the community will chime in with a solution they have already developed.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.