How to create DAX query with two date dimension table

Question

I have two date dimension table called Date_Week and Date_Month, these tables have a relationship with the Fact table date field. I want to create a measure that rolls up the usage field from the fact for the last 7 days and the query which I created works fine with one date dimension which is used for the calculation (Date_Week). Is it possible to make it work with both the date dimension based on which dimension is used as a row value?

Below is the DAX query I have created.

Rolling Usage 7 days:=
CALCULATE(
SUM('Fact'Usage),
DATESBETWEEN(Date_week[Day],LASTDATE('Fact'[Date])-7,LASTDATE('Fact'[Date])))

Below are the table and Expected result

Is there any reason why you have two date tables? can you not combine to have a standard calendar for time intelligence functions? — Jon, Dec 01 '20 at 22:31
No, We have some other attributes in the dimension tables which cannot be merged — sreeprasad k s, Dec 04 '20 at 05:37

score 0 · Answer 1 · answered Jan 02 '21 at 00:39

Since conditional functions like IF and SWITCH can only return a scalar and not a table, I'm afraid the only way is to check what table is filtered and then duplicate the CALCULATE statement applying the filtering Date table.

Rolling Usage 7 days :=
IF(
    ISFILTERED( Date_week ),
    CALCULATE(
        SUM( 'Fact'Usage ),
        DATESBETWEEN( Date_week[Day], MAX( 'Fact'[Date] ) - 7, MAX( 'Fact'[Date] ) )
    ),
    CALCULATE(
        SUM( 'Fact'Usage ),
        DATESBETWEEN( Date_month[Day], MAX( 'Fact'[Date] ) - 7, MAX( 'Fact'[Date] ) )
    )
)

How to create DAX query with two date dimension table

1 Answers1