Forum Discussion

coast2coast's avatar
coast2coast
New Member
2 years ago
Solved

DAX Measure Function DATESINPERIOD - sum while excluding current week

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, how...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
2 years ago

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.