2 calandar Week back

[netanel]

Hi All,

iam trying to get 2 calander week back (not 14 days)

this is my measure 

SalesTwoWeeksAgo =
CALCULATE (
    SUM ( 'Table'[SalesAmount] ),
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                'Table',
                EARLIER ( 'Table'[Date] ) = DATEADD ( 'Table'[Date], -14, DAY )
            )
        )
    )
)

[Anonymous · Accepted solution]

Hi  @netanel ,

You can modify the DAX formula to the following:

Measure =
var _week=WEEKNUM(MAX('Table'[Date]),2)
var _lastweek=WEEKNUM(MAX('Table'[Date]),2)-1
var _nextweek=WEEKNUM(MAX('Table'[Date]),2)+1
var _mindate=
MINX(
    FILTER(ALL('Table'),WEEKNUM('Table'[Date],2) = _lastweek),'Table'[Date])
var _maxdate=
MAXX(
    FILTER(ALL('Table'),WEEKNUM('Table'[Date],2) = _nextweek),'Table'[Date])
return
CALCULATE (
    SUM ( 'Table'[SalesAmount] ),
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                'Table',
               'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate
            )
        )
    )
)

Best Regards,

Liu Yang

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