Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate if selected date is between two dates

Hi everyone!

 

I am struggling with a difficult problem. I have a fact table with 2 dates: Task Start Date and Task End Date. I have another dimension table (calendar table) with 2 inactive relationships between these 2 dates and calendar date. In calendar table I have information about Year and month.

 

How to calculate it in dax? I have tried with something using selectedvalue(calendar[year]), selectedvalue(calendar[month]) and combine it with these 2 dates, but it is incorrect. 1 should be inserted if calendar Year and calendar Month match to Year and Month these 2 dates. 

 

DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 

    please use 

    =
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR MinDate =
    MIN ( 'Calendar'[Date] )
RETURN
    SUMX (
        TableName,
        IF ( TableName[StartDate] <= MaxDate && TableName[EndDate] >= MinDate, 1 )
    )

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

    please use 

    =
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR MinDate =
    MIN ( 'Calendar'[Date] )
RETURN
    SUMX (
        TableName,
        IF ( TableName[StartDate] <= MaxDate && TableName[EndDate] >= MinDate, 1 )
    )