Measure Date range

[PKPK90]

The issue is that despite applying the same date filter, two measures—SumQuantityADJ and SumQuantity6Weeks—are returning different results. Both measures are intended to sum values from the 'MaterialTransactions'[QuantityADJ_Positive] column over a 6-week period ending on EndDate_. However, they are not yielding the same totals, which suggests there might be a discrepancy in how the date filter is being applied or potentially other filters influencing the results that are not immediately apparent.

The objective was to have the entire SUMMARIZECOLUMNS function cover a date range of 18 weeks, while the measure SumQuantity6Weeks was specifically designed to calculate over a 6-week period. However, the results from SumQuantity6Weeks were not accurate. To troubleshoot the issue, I'm testing both the SUMMARIZECOLUMNS and the SumQuantity6Weeks measure using the same 6-week date range to ensure consistency and pinpoint the discrepancy.

DEFINE
    VAR StartDate_ = @StartDate_
    VAR EndDate_ = @EndDate_
    VAR Site_ = @Site_
    VAR Source_ = @Source_
    VAR Division_ = @Division_
    VAR Type_ = @Type_
    VAR Item_ = @Item_
    VAR SS_ = @SS_
    VAR Status_ = @Status_
    VAR NewDateFilter_ =
        FILTER (
            'Calendar',
            'Calendar'[Date]
                >= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 2 ) - ( 6 * 7 ) + 1
                && 'Calendar'[Date]
                    <= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 1 ) + 1
        )

EVALUATE
SUMMARIZECOLUMNS (
    'Items'[Site],
    'Items'[ProductDivisionDesc],
    'Items'[ProductFamilyDesc],
    'Items'[Source],
    'Items'[Item],
    'Items'[ItemDesc],
    'Items'[QuantityOnHand],
    'Items'[NonNetableStock],
    'Items'[SafetyStock],
    'Items'[UnitCost],
    'Items'[LeadTimeFixed],
    'Items'[MaterialStatus],
    'Calendar'[Year],
    'Calendar'[Week],
    FILTER (
        'Calendar',
        'Calendar'[Date]
            >= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 2 ) - ( 6 * 7 ) + 1
            && 'Calendar'[Date]
                <= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 1 ) + 1
    ),
    FILTER ( VALUES ( Items[Site] ), PATHCONTAINS ( Site_, Items[Site] ) ),
    FILTER ( VALUES ( Items[Source] ), PATHCONTAINS ( Source_, Items[Source] ) ),
    FILTER (
        VALUES ( Items[ProductDivisionDesc] ),
        PATHCONTAINS ( Division_, Items[ProductDivisionDesc] )
    ),
    FILTER ( VALUES ( Items[Item] ), PATHCONTAINS ( Item_, Items[Item] ) ),
    FILTER (
        VALUES ( Items[MaterialStatus] ),
        PATHCONTAINS ( Status_, Items[MaterialStatus] )
    ),
    FILTER (
        VALUES ( MaterialTransactions[TransactionType] ),
        PATHCONTAINS ( Type_, MaterialTransactions[TransactionType] )
    ),
    FILTER (
        VALUES ( Items[SafetyStock] ),
        PATHCONTAINS ( SS_, Items[SafetyStock] )
    ),
    "SumQuantity", CALCULATE ( ( SUM ( 'MaterialTransactions'[Quantity] ) ) + 0 ),
    "SumQuantityADJ", CALCULATE ( SUM ( 'MaterialTransactions'[QuantityADJ_Positive] ) + 0 ),
    "TotalQuantity", 'MaterialTransactions'[TotalQuantity] + 0,
    "SumQuantity6Weeks",
        CALCULATE (
            SUM ( 'MaterialTransactions'[QuantityADJ_Positive] ) + 0,
            NewDateFilter_
        ),
    "Rank",
        RANKX (
            ALLSELECTED ( 'Calendar'[Date] ),
            CALCULATE ( FIRSTDATE ( 'Calendar'[Date] ) ),
            ,
            ASC,
            DENSE
        )
)

[Anonymous · Accepted solution]

Hi @PKPK90 ,

To your question below are some of my thoughts:

1. Date Filter Application: Ensure that the NewDateFilter_ variable is correctly defined and applied in both measures. The date range calculation should be consistent.

2. Measure Context: The context in which SumQuantityADJ and SumQuantity6Weeks are calculated might differ. Check if there are any additional filters or context changes affecting one measure but not the other.

3. SUMMARIZECOLUMNS Function: Verify that the SUMMARIZECOLUMNS function is correctly applying the date filter. The date range should be consistent across all measures.

4. I think you could also just use the following string of DAX code: 

SumQuantity6Weeks_Debug =
CALCULATE (
    SUM ( 'MaterialTransactions'[QuantityADJ_Positive] ),
    FILTER (
        'Calendar',
        'Calendar'[Date]
            >= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 2 ) - ( 6 * 7 ) + 1
            && 'Calendar'[Date]
                <= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 1 ) + 1
    )
)

5. Also if possible I would like you to provide example data for further research.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.