PREVIOUS WEEK DAX HELP

[Merle]

I have used these Dax formula to calculate Previous Week but results are incorrect, can someone please help me rectify this? Thanks in advance.

First Formula used populates column but values are incorrect.

Star_Weekly_TOTAL_ACTIONS_LAST_WEEK =

VAR LastWeek =

    MIN( STAR_PLUS_WEEKLY_ACTIONS_VW[DATE])

RETURN

    CALCULATE (

        SUM ( STAR_PLUS_WEEKLY_ACTIONS_VW[TOTAL_ACTIONS] )

      )

    -

      CALCULATE

            ( SUM(STAR_PLUS_WEEKLY_ACTIONS_VW[TOTAL_ACTIONS] ),

             STAR_PLUS_WEEKLY_ACTIONS_VW[DATE] = LastWeek)

Results

Previous Week in Second Forumula shows blank Values

Weekly_Previous_week =

VAR CURRENTWEEK = SELECTEDVALUE('Calendar'[Week of Year])

VAR CURRENTYEAR = SELECTEDVALUE('Calendar'[Year])

VAR MAXWEEKNUMBER = CALCULATE(MAX('Calendar'[Week of Year]), ALL('Calendar'))

RETURN

SUMX(

    FILTER(ALL('Calendar'),

        IF(CURRENTWEEK = 1,

            'Calendar'[Week of Year] = MAXWEEKNUMBER && 'Calendar'[Year] = CURRENTYEAR - 1,

            'Calendar'[Week of Year] = CURRENTWEEK - 1 && 'Calendar'[Year] = CURRENTYEAR)),

    [STAR_WEEKLY_TOTAL_ACTIONS_THIS_WEEK])

Results

[Merle]

@ValtteriN Can you assist? I do not have lastWeek as a table to filter on, so I used calendar and here are my results.

Dax Formula:

  var weeknumLW = WEEKNUM(TODAY())-1

  VAR __DS0FilterTable =

    FILTER(

      'Calendar',

        WEEKNUM('Calendar'[Date])=weeknumLW

      )  

    return  

weeknumLW

[Anonymous]

Hi @Merle,

You can try to use the following formula to get the previous week amounts:

formula =
VAR currdate =
    MAX ( Calendar[Date] )
VAR weekStart =
    IF (
        WEEKNUM ( currdate, 1 ) <> 1,
        currdate - WEEKDAY ( currdate, 1 ) + 1,
        DATE ( YEAR ( currdate ), 1, 1 )
    )
RETURN
    CALCULATE (
        SUM ( Table[TOTAL_ACTIONS] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] )
                = YEAR ( weekStart - 1 )
                && WEEKNUM ( [Date] , 1 )
                    = WEEKNUM ( weekStart - 1 , 1 )
        )
    )

Regards,

Xiaoxin Sheng

[Merle]

Hi Valtterin,

Thank you for your response. The results I am looking for is Actions for the last week to the current week values not the actual week num.

[ValtteriN]

Hi,

Try using WEEKNUM e.g. 

LastWeek =

  var weeknumLW = WEEKNUM(TODAY())-1

  VAR __DS0FilterTable =

    FILTER(

      'LastWeek',

        WEEKNUM('LastWeek'[Date])=weeknumLW

      )

    return

weeknumLW

By utilizing WEEKNUM formula you should be able to get previous week's values.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/