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

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.