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.

     

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

It seems you use it as a calculated column. Maybe you can try it like this:

 

Day Count of Drop =
[Start Date] - [Campaign Creation Date]

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    v-jiascu-msft

     

    Dale, thanks for the input.  Two questions:

     

    1.  There are some instances where my Start Date is before my Creation Date.  Although this is an error, I am not sure your proposed solution will work.  Thoughts?

     

    2.  How does your solution solve for my business day need?

     

    Thanks.