Am I fundamentally misunderstanding date tables?

[Anonymous]

Hi Everyone  -  So, I have a calendar table that I have marked as a date table.   The calendar table is one downloaded from Marco's site, so I know it is legitimate.  

I have a shipping table...with dates of when orders have shipped.   There are other dates in that table as well, such as invoice date, etc.

I have a relationship setup between "date shipped" in the shipping table, and the Date field in my Date Table.   

I had thought that these two dates could be used interchangeably once the relationhip was created, and the date table was formally recognized as a "date table".    And furthermore, that you could essentially use the date table for ALL date related measures since it was linked to multiple other tables that have dates (such as my Orders table, the Shipping Table, etc).   

However, I am finding that when a TotalMTD calculation, I must use the date in the shipping table.   If I try and use the Date Table, I get (Blank).     TotalYTD works....but TotalMTD does not.    

Likewise if I try a week to date calculation, such as the one below, I also have to use the dates from my shipping table...I get an error if try and use the date table.  

Week to Date Shipments2 =

var CurrentDate=LASTDATE('FLU_Shipped Orders'[Date Shipped])

var DayNumberOfWeek=WEEKDAY(LASTDATE('FLU_Shipped Orders'[Date Shipped]),3)

return

CALCULATE(sum('FLU_Shipped Orders'[Shipped Price]),

DATESBETWEEN(

    'FLU_Shipped Orders'[Date Shipped],

DATEADD(

    CurrentDate,

    -1*DayNumberOfWeek,

    DAY),

    CurrentDate))

[v-shex-msft]

Hi @Anonymous ,

I'm not so sure for your data structure and relationships, can you please share some sample data for test? 

How to Get Your Question Answered Quickly
In addition, you can try to use date functions to manually calculate filtered date range:

Week to Date Shipments2 =
VAR CurrentDate =
    MAX ( Calendar[Date] )
VAR DayNumberOfWeek =
    WEEKDAY ( CurrentDate, 3 )
RETURN
    CALCULATE (
        SUM ( 'FLU_Shipped Orders'[Shipped Price] ),
        FILTER (
            ALLSELECTED ( 'FLU_Shipped Orders' ),
            'FLU_Shipped Orders'[Date Shipped]
                >= DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ), DAY ( CurrentDate ) - 1 * DayNumberOfWeek )
                && 'FLU_Shipped Orders'[Date Shipped] <= CurrentDate
        )
    )

Regards,

Xiaoxin Sheng