Measure Date range

Question

The issue is that despite applying the same date filter, two measures—SumQuantityADJ&nbsp;and&nbsp;SumQuantity6Weeks—are returning different results. Both measures are intended to sum values from the&nbsp;'MaterialTransactions'[QuantityADJ_Positive]&nbsp;column over a 6-week period ending on&nbsp;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&nbsp;SUMMARIZECOLUMNS&nbsp;function cover a date range of 18 weeks, while the measure&nbsp;SumQuantity6Weeks&nbsp;was specifically designed to calculate over a 6-week period. However, the results from&nbsp;SumQuantity6Weeks&nbsp;were not accurate. To troubleshoot the issue, I'm testing both the&nbsp;SUMMARIZECOLUMNS&nbsp;and the&nbsp;SumQuantity6Weeks&nbsp;measure using the same 6-week date range to ensure consistency and pinpoint the discrepancy.DEFINE&nbsp;&nbsp;&nbsp;&nbsp;VAR StartDate_ = @StartDate_&nbsp;&nbsp;&nbsp;&nbsp;VAR EndDate_ = @EndDate_&nbsp;&nbsp;&nbsp;&nbsp;VAR Site_ = @Site_&nbsp;&nbsp;&nbsp;&nbsp;VAR Source_ = @Source_&nbsp;&nbsp;&nbsp;&nbsp;VAR Division_ = @Division_&nbsp;&nbsp;&nbsp;&nbsp;VAR Type_ = @Type_&nbsp;&nbsp;&nbsp;&nbsp;VAR Item_ = Item_&nbsp;&nbsp;&nbsp;&nbsp;VAR SS_ = SS_&nbsp;&nbsp;&nbsp;&nbsp;VAR Status_ = @Status_&nbsp;&nbsp;&nbsp;&nbsp;VAR NewDateFilter_ =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Date]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&gt;= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 2 ) - ( 6 * 7 ) + 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp; 'Calendar'[Date]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 1 ) + 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)EVALUATESUMMARIZECOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;'Items'[Site],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[ProductDivisionDesc],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[ProductFamilyDesc],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[Source],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[Item],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[ItemDesc],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[QuantityOnHand],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[NonNetableStock],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[SafetyStock],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[UnitCost],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[LeadTimeFixed],&nbsp;&nbsp;&nbsp;&nbsp;'Items'[MaterialStatus],&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Year],&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Week],&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Date]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&gt;= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 2 ) - ( 6 * 7 ) + 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp; 'Calendar'[Date]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 1 ) + 1&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER ( VALUES ( Items[Site] ), PATHCONTAINS ( Site_, Items[Site] ) ),&nbsp;&nbsp;&nbsp;&nbsp;FILTER ( VALUES ( Items[Source] ), PATHCONTAINS ( Source_, Items[Source] ) ),&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES ( Items[ProductDivisionDesc] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PATHCONTAINS ( Division_, Items[ProductDivisionDesc] )&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER ( VALUES ( Items[Item] ), PATHCONTAINS ( Item_, Items[Item] ) ),&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES ( Items[MaterialStatus] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PATHCONTAINS ( Status_, Items[MaterialStatus] )&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES ( MaterialTransactions[TransactionType] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PATHCONTAINS ( Type_, MaterialTransactions[TransactionType] )&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES ( Items[SafetyStock] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PATHCONTAINS ( SS_, Items[SafetyStock] )&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"SumQuantity", CALCULATE ( ( SUM ( 'MaterialTransactions'[Quantity] ) ) + 0 ),&nbsp;&nbsp;&nbsp;&nbsp;"SumQuantityADJ", CALCULATE ( SUM ( 'MaterialTransactions'[QuantityADJ_Positive] ) + 0 ),&nbsp;&nbsp;&nbsp;&nbsp;"TotalQuantity", 'MaterialTransactions'[TotalQuantity] + 0,&nbsp;&nbsp;&nbsp;&nbsp;"SumQuantity6Weeks",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM ( 'MaterialTransactions'[QuantityADJ_Positive] ) + 0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NewDateFilter_&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"Rank",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RANKX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED ( 'Calendar'[Date] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE ( FIRSTDATE ( 'Calendar'[Date] ) ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ASC,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DENSE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;))

anonymous · Accepted Answer

Hi&nbsp;PKPK90&nbsp;,
To your question below are some of my thoughts:
&nbsp;
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.
&nbsp;
4.&nbsp;I think you could also just use the following string of DAX code:&nbsp;
SumQuantity6Weeks_Debug =
CALCULATE (
    SUM ( 'MaterialTransactions'[QuantityADJ_Positive] ),
    FILTER (
        'Calendar',
        'Calendar'[Date]
            &gt;= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 2 ) - ( 6 * 7 ) + 1
            &amp;&amp; 'Calendar'[Date]
                &lt;= DATEVALUE ( EndDate_ ) - WEEKDAY ( DATEVALUE ( EndDate_ ), 1 ) + 1
    )
)
&nbsp;
5.&nbsp;Also if possible I would like you to provide example data for further research.
&nbsp;
&nbsp;
Best Regards
Yilong Zhou
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Measure Date range

1 Reply

Related content

Date Range Slicer between Dynamic Dates

Custom Date Range Slicer

Creating a new measure from date ranges

Limiting date range slicer

PowerBI Measure for Colouring Date Ranges