topic Re: PREVIOUS WEEK DAX HELP in DAX Commands and Tips

PREVIOUS WEEK DAX HELP

Merle — Thu, 06 Apr 2023 05:02:15 GMT

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

Re: PREVIOUS WEEK DAX HELP

ValtteriN — Thu, 06 Apr 2023 06:13:31 GMT

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/

Re: PREVIOUS WEEK DAX HELP

Merle — Thu, 06 Apr 2023 07:14:02 GMT

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.

Re: PREVIOUS WEEK DAX HELP

Merle — Thu, 06 Apr 2023 21:07:56 GMT

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

Re: PREVIOUS WEEK DAX HELP

Anonymous — Wed, 12 Apr 2023 03:14:31 GMT

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