DAX Measure Function DATESINPERIOD - sum while excluding current week

[coast2coast]

Trying to use these measures to sum values 3 weeks before and after current week, while excluding current week values. 

These measures work to get the correct values for the current week and ID, however the 3 weeks includes the current week. 

Simplest way to create these 2 measures to exclude current week? Thank you!!!

3WeekForward = var _result = CALCULATE(SUMX(Query1, Query1[DAYS]),

DATESINPERIOD(Query1[WEEK_START_DATE], MIN(Query1[WEEK_START_DATE]), 21, DAY)) RETURN _result

3WeekLookback = var _result = CALCULATE(SUMX(Query1, Query1[DAYS]),

DATESINPERIOD(Query1[WEEK_START_DATE], MIN(Query1[WEEK_START_DATE]), -21, DAY)) RETURN _result

[Anonymous · Accepted solution]

HI @coast2coast,

Perhaps you can try to use date function to manually calculate the date range for the calculated conditions:

3weekPrev =
VAR currDate =
    MAX ( Query1[WEEK_START_DATE] )
VAR eDate =
    currDate - WEEKDAY ( currDate, 1 )
RETURN
    CALCULATE (
        SUM ( Query1[DAYS] ),
        FILTER (
            ALLSELECTED ( Query1 ),
            Query1[WEEK_START_DATE]
                >= DATE ( YEAR ( eDate ), MONTH ( eDate ), DAY ( eDate ) - 20 )
                && Query1[WEEK_START_DATE] <= eDate
        )
    )

3weekForward =
VAR currDate =
    MAX ( Query1[WEEK_START_DATE] )
VAR sDate =
    currDate - WEEKDAY ( currDate, 1 ) + 7
RETURN
    CALCULATE (
        SUM ( Query1[DAYS] ),
        FILTER (
            ALLSELECTED ( Query1 ),
            Query1[WEEK_START_DATE] > sDate
                && Query1[WEEK_START_DATE]
                    <= DATE ( YEAR ( sDate ), MONTH ( sDate ), DAY ( sDate ) + 21 )
        )
    )

Regards,

Xiaoxin Sheng

[coast2coast]

Thank you for the suggestion - I could not get it to work properly with a sum function - it didn't sum and only pulled the value 3 weeks prior or later or not at all. 

[Anonymous]

Hi @coast2coast ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[jdbuchanan71]

@coast2coast 

Try it with DATESBETWEEN becasue you can control both the start and the end.  Build the list of dates and apply it as a filter in your CLACULATE

Before = 
VAR _Max = MAX ( DATES[Date] )
VAR _Dates = DATESBETWEEN ( DATES[Date], _Max-21, _Max-7 )
RETURN
    CALCULATE( [Paid Amount], _Dates )

This builds the list of dates between the max date -21 and the max date -7 then applies that as the filter in the CALCULATE.

The _Dates variable builds the list of dates in red then calculates the amount.

The slicers are only tied to the table below them so the one on the left is only looking at todays date but it is getting the 15 days worth of the amounts from the measure.