difference between dates

[usman96]

How To Count The Days Excluding Sundays Between Two Dates  ? 

[Tertxu]

Hey, Im struggling calculating average of a column which contains difference between two dates. When calculating in excel the results is ok, but not in power bi. 

Steps I´ve followed (as an exacmple):

1; If (B1=blank():blank():(B1-A1))

2. New measure: average column

Any suggestion?

[Ashish_Mathur]

Hi,

1. Is your IF formual a calculated column or a measure?
2. What measure have you written for computing the average?
3. What result are you getting with that measure?
4. What result are ou expecting?
5. What does your data look like?

[Tertxu]

Hey, 

Let me explain myself a little better. In excel I have the following:

Date Created    Date Assigned    Date Finished

Some of them are blank. I want to have three measures: 

1, Days between Date created and date finished. After that, I want to calculate the average excluding blanks

2. Days between date created and date assigned. After that, I want to calculate the average excluding blanks

3. Days between date assigned and date finished. After that, I want to calculate the average excluing blanks. 

The "new column" im including in power bi is just as simple as: if(date assigned=blank();blank();(date assigned-date created)

The "new measure" = average(new column)

The results in excel are accurate (average aprox 1.2) and in power bi are over 30 days, which doesnt make sense... 

Any ideas? Its driving my crazy 😞

[Ashish_Mathur]

That's strange.  Share the link from where i can download your PBI file.

[Zubair_Muhammad]

Hi @usman96

You can use this calculated column....assuming you have Startdate and Enddate columns

Count Business Days =
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES ( TableName[StartDate] + 1, TableName[ EndDate] ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    COUNTROWS ( FILTER ( mydates, [Day] <= 6 ) )

[usman96]

what is "value" in your formula

[Zubair_Muhammad]

@usman96

It is the temporary Column created by the temporary table

GENERATESERIES ( TableName[StartDate] + 1, TableName[ EndDate] ),

for each row of your table

[Zubair_Muhammad]

@usman96

Please see the attached file here

[usman96]

I only have date column  , not starting  date and ending date

[Zubair_Muhammad]

@usman96

Then what difference do you want to count if you have a single date?

You can copy paste some sample data here with expected results

For example

StartDate	EndDate	Count Business Days
Monday, January 1, 2018	Saturday, January 13, 2018	11
Tuesday, January 9, 2018	Thursday, January 18, 2018	8
Thursday, January 18, 2018	Wednesday, January 31, 2018	11

[usman96]

I have a single column like this and  i want to find how many days between these two dates (exlcuding sunday) 

(as u can see , one is smaller date and one is larger )

[Zubair_Muhammad]

@usman96

Do you have only 2 dates in your Column?

[usman96]

yes for now  , but as enter more data .............. the table structure should be like  in Ascending order  (Smaller dates to larger dates)

[Zubair_Muhammad]

@usman96

Suppose you have a table like this

Date

Monday, January 1, 2018

Saturday, January 13, 2018

Sunday, January 21, 2018

Saturday, February 3, 2018

You can add a calculated column to get the difference from next date using this formula

Difference from Next date =
VAR NextDate =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER ( ALL ( Table1 ), Table1[Date] > EARLIER ( Table1[Date] ) )
    )
VAR Next_date =
    IF ( ISBLANK ( NextDate ), Table1[Date] + 1, NextDate )
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES ( Table1[Date] + 1, Next_Date ),
        "Day", WEEKDAY ( [Value], 2 )
    )
RETURN
    COUNTROWS ( FILTER ( mydates, [Day] <= 6 ) )

[Zubair_Muhammad · Accepted solution]

@usman96

Please see the attached file

[usman96]

Thanks 🙂 

[usman96]

what should i do ?