Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Day Count Business Days Between Two Dates

I am currently using the following function for Day Count:   Day Count of Drop = SWITCH( TRUE(), 'KPI Dashboard AD'[Campaign Creation Date]<'KPI Dashboard AD'[Start Date], DATEDIFF('KPI Dashboard...
Need Help
Show and Tell
Tips and Tricks
  • erik_tarnvik's avatar
    erik_tarnvik
    8 years ago

    I intended it as a measure.

     

    The culprit is this statement:

     

    Business Day Value = if('Date'[DayOfWeek]="Monday"||
                            'Date'[DayOfWeek]="Tuesday"||
                            'Date'[DayOfWeek]="Wednesday"||
                            'Date'[DayOfWeek]="Thursday"||
                            'Date'[DayOfWeek]="Friday",
                            "1",
                            "0")

    This statement results in a string of either "1" or "0". Change this to:

    Business Day Value = if('Date'[DayOfWeek]="Monday"||
                        'Date'[DayOfWeek]="Tuesday"||
                        'Date'[DayOfWeek]="Wednesday"||
                        'Date'[DayOfWeek]="Thursday"||
                        'Date'[DayOfWeek]="Friday",
                        1,
                        0)

    and make sure that the Business Day Value column in your calendar table is of type Whole Number. That should do it.

     

erik_tarnvik's avatar
erik_tarnvik
Icon for Solution Specialist rankSolution Specialist
8 years ago

 

Anonymous

 

 

Sure! I think you are heading in the right direction, the Business Day Value column is exactly what i had in mind.  

 

Regarding your two questions, I was referring to your Switch statement where you want to account for the case where you want a negative value as a result, in that case you need to compare the two dates to see which is smaller than the other and account for that. The CALCULATE simply filters the calendar table to contain only the dates between your cutoffs and then sums up the working dates. See below for an example, you may need to adjust somewhat, I haven't tested this but it should give you the right idea. 

 

Day Count of Drop = 
IF('KPI Dashboard AD'[Campaign Creation Date]<'KPI Dashboard AD'[Start Date], 
    CALCULATE(Sum('Date'[Business Day Value]), 
              FILTER('Date', 'Date'[DateColumn] >= 'KPI Dashboard AD'[Campaign Creation Date] && 
                             'Date'[DateColumn] < 'KPI Dashboard AD'[Start Date])),
    CALCULATE(Sum('Date'[Business Day Value]), 
              FILTER('Date', 'Date'[DateColumn] <= 'KPI Dashboard AD'[Campaign Creation Date] && 
                             'Date'[DateColumn] > 'KPI Dashboard AD'[Start Date])) * -1
)

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

This is a little out of my depth, as I am still a newbie.

 

I think the only hang up is the valye that should be in Date[Date Column] - not sure what this should be?

 

 

This is my statement for my date table...

 

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthShortYear",FORMAT([Date], "mmm-YYYY"),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"Week Number", WEEKNUM ( [Date] ),
"Week Number and Year", "W" & WEEKNUM ( [Date] ) & " " & YEAR ( [Date] ),
"WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] )
)

 

Thanks again!

  • erik_tarnvik's avatar
    erik_tarnvik
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    I used DateColumn since I could not know what name you had chosen for your date column in your calendar. So, replace 'Date'[DateColumn] with 'Date'[Date].

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      erik_tarnvik

       

      Thanks for the continued help...This is the most recent result.

       

      Int neither works a s a Measure or Column, not sure whoch it should be.

       

      Any suggestions?

       

      • erik_tarnvik's avatar
        erik_tarnvik
        Icon for Solution Specialist rankSolution Specialist
        8 years ago

        I intended it as a measure.

         

        The culprit is this statement:

         

        Business Day Value = if('Date'[DayOfWeek]="Monday"||
                                'Date'[DayOfWeek]="Tuesday"||
                                'Date'[DayOfWeek]="Wednesday"||
                                'Date'[DayOfWeek]="Thursday"||
                                'Date'[DayOfWeek]="Friday",
                                "1",
                                "0")

        This statement results in a string of either "1" or "0". Change this to:

        Business Day Value = if('Date'[DayOfWeek]="Monday"||
                        'Date'[DayOfWeek]="Tuesday"||
                        'Date'[DayOfWeek]="Wednesday"||
                        'Date'[DayOfWeek]="Thursday"||
                        'Date'[DayOfWeek]="Friday",
                        1,
                        0)

        and make sure that the Business Day Value column in your calendar table is of type Whole Number. That should do it.