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, 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
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

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super 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.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • coast2coast's avatar
    coast2coast
    New Member
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    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