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
kinga
Helper I
Helper I

difference between two days excluding weekends

Hello, I have the below formula. I am looking to get this to exclude weekends. Any suggestions?
 
TU 1 OnTime DEL Tracking = if(ISNUMBER(tCostAndCycles_TUDetails[TU_1SCHDELDate].[Date]),if(ISNUMBER(tCostAndCycles_TUDetails[TU_1ACTDELDate].[Date]),if(ISNUMBER(tCostAndCycles_TUDetails[TU_1ActDelDate_CapturedDate].[Date]),if(tCostAndCycles_TUDetails[TU_1ACTDELDate] > DATE(2018,10,1),1.0*[TU_1ActDelDate_CapturedDate]-[TU_1ACTDELDate]),BLANK()),BLANK()))
 
Capture.PNG
5 REPLIES 5
kinga
Helper I
Helper I

I proceeded to use the below - if(ISNUMBER(tCostAndCycles_TUDetails[TU_1SCHDELDate].[Date]),if(ISNUMBER(tCostAndCycles_TUDetails[TU_1ACTDELDate].[Date]),if(ISNUMBER(tCostAndCycles_TUDetails[TU_1ActDelDate_CapturedDate].[Date]),if(tCostAndCycles_TUDetails[TU_1ACTDELDate] >= DATE(2018,10,1),if(tCostAndCycles_TUDetails[TU_1ACTDELDate]<= DATE(2018,12,31),CALCULATE ( SUM ( 'Calendar Table'[IsWorkDay] ), DATESBETWEEN ( 'Calendar Table'[Date], 'tCostAndCycles_TUDetails'[TU_1ACTDELDate], 'tCostAndCycles_TUDetails'[TU_1ActDelDate_CapturedDate] ) ) - 1,BLANK()),BLANK()))))

Hi @kinga

If your write the formula as 

IsWeekDay = IF(WEEKDAY(CalendarTable[Date];2)<=5,1)

then your formula in the last post should work

measure =
IF (
    ISNUMBER ( tCostAndCycles_TUDetails[TU_1SCHDELDate].[Date] ),
    IF (
        ISNUMBER ( tCostAndCycles_TUDetails[TU_1ACTDELDate].[Date] ),
        IF (
            ISNUMBER ( tCostAndCycles_TUDetails[TU_1ActDelDate_CapturedDate].[Date] ),
            IF (
                tCostAndCycles_TUDetails[TU_1ACTDELDate] >= DATE ( 2018, 10, 1 ),
                IF (
                    tCostAndCycles_TUDetails[TU_1ACTDELDate] <= DATE ( 2018, 12, 31 ),
                    CALCULATE (
                        SUM ( 'Calendar Table'[IsWorkDay] ),
                        DATESBETWEEN (
                            'Calendar Table'[Date],
                            MAX('tCostAndCycles_TUDetails'[TU_1ACTDELDate]),
                            MAX('tCostAndCycles_TUDetails'[TU_1ActDelDate_CapturedDate])  
//IF yhis formula is a measure,and
// if your original formula doesn't work,
// please modify as this by adding "MAX" function ) ) - 1, BLANK () ), BLANK () ) ) ) )

Best Regards

Maggie

v-juanli-msft
Community Support
Community Support

Hi @kinga

Would you like 

tCostAndCycles_TUDetails[TU_1ACTDELDate] > DATE ( 2018101 ) and not=weekends ?

or 

1.0 * [TU_1ActDelDate_CapturedDate]  (not include weekends)
                    - [TU_1ACTDELDate]  (not include weekends)   ?

 

 

TU 1 OnTime DEL Tracking =
IF (
    ISNUMBER ( tCostAndCycles_TUDetails[TU_1SCHDELDate].[Date] ),
    IF (
        ISNUMBER ( tCostAndCycles_TUDetails[TU_1ACTDELDate].[Date] ),
        IF (
            ISNUMBER ( tCostAndCycles_TUDetails[TU_1ActDelDate_CapturedDate].[Date] ),
            IF (
                tCostAndCycles_TUDetails[TU_1ACTDELDate] > DATE ( 2018101 ),
                1.0 * [TU_1ActDelDate_CapturedDate]
                    - [TU_1ACTDELDate]
            ),
            BLANK ()
        ),
        BLANK ()
    )
)



 

Best Regards

Maggie

Hello,

 

For any records that have a TU_1ACTDELDate > DATE (2018, 10, 1 ), it would need to calculate the difference in days between TU_1ActDelDate_CapturedDate and TU_1ACTDELDate, excluding weekends.

 

Does this help to clarify?

 

Vvelarde
Community Champion
Community Champion

@kinga

 

Hi, This is a sample: (You need to adapt to your needs)

 

In your calendar table add a calculated column:

 

IsWeekend = IF(WEEKDAY(CalendarTable[Date];2)>5,"Y","N")

Create a measure:

 

DiffDaysWithoutWeekends =
VAR _Start =
    SELECTEDVALUE ( Table1[StartDate] )
VAR _End =
    SELECTEDVALUE ( Table1[EndDate] )
RETURN
    IF (
        HASONEVALUE ( Table1[ID] ),
        CALCULATE (
            COUNT ( CalendarTable[Date] ),
            FILTER (
                ALL ( CalendarTable ),
                CalendarTable[Date] >= _Start
                    && CalendarTable[Date] <= _End
                    && CalendarTable[IsWeekend] = "N"
            )
        )
    )

Regards

 

Victor

 




Lima - Peru

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.