Forum Discussion

netanel's avatar
netanel
Post Prodigy
3 years ago
Solved

2 calandar Week back

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 )
            )
        )
    )
)
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.