Forum Discussion

ClearRide's avatar
ClearRide
Frequent Visitor
3 years ago
Solved

Count Business days between Dates

Here are my tables: 

'Promotion Table'

'Date Table'

I need a calculated column in the Promotion table called "Days in Promotion" which simply counts the number of days in a promotion BUT it needs to abide by the date table's BusinessDATEFlag Field. Meaning that day will only be counted if BusinessDateFlag = 1. 
The 'Date Table' contains every day from 1-1-2020 to 12-31-2022.


This is my DAX right now but it's not working (it comes up blank)

Days in Promotion =
VAR BeginDate = Promotion[PromotionBegDate]
VAR EndDate = Promotion[PromotionEndDate]
VAR DaysCount = CALCULATE(
    COUNTROWS ( 'Date Table'),
    DATESBETWEEN ( 'Date Table'[Business Date], EndDate,BeginDate),
    'Date Table'[BusinessDayFlag] = 1
)
RETURN DaysCount
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    ClearRide Try:

    Days in Promotion =
VAR BeginDate = Promotion[PromotionBegDate]
VAR EndDate = Promotion[PromotionEndDate]
VAR DaysCount = 
    COUNTROWS ( FILTER('Date Table', 'Date Table'[BusinessDayFlag] = 1 && 'Date Table'[Business Date] >= BeginDate &&  'Date Table'[Business Date] <= EndDate)
    )
RETURN DaysCount

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    ClearRide Try:

    Days in Promotion =
VAR BeginDate = Promotion[PromotionBegDate]
VAR EndDate = Promotion[PromotionEndDate]
VAR DaysCount = 
    COUNTROWS ( FILTER('Date Table', 'Date Table'[BusinessDayFlag] = 1 && 'Date Table'[Business Date] >= BeginDate &&  'Date Table'[Business Date] <= EndDate)
    )
RETURN DaysCount
  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    3 years ago

    Check Greg_Deckler answer, simplified and faster. I added this reply to show the correction to your formula.

    BeginDate has to be first in your datesbetween function. 

     

     

    Days in Promotion =
VAR BeginDate = Promotion[PromotionBegDate]
VAR EndDate = Promotion[PromotionEndDate]
VAR DaysCount = CALCULATE(
    COUNTROWS ( 'Date Table'),
    DATESBETWEEN ( 'Date Table'[Business Date], BeginDate,EndDate),
    'Date Table'[BusinessDayFlag] = 1
)
RETURN DaysCount