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.

     

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.