topic Re: DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE in DAX Commands and Tips

DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

cuohanele — Sat, 15 Aug 2020 15:28:52 GMT

Hello community!,

I am having a hard time figuring out a dynamic DAX that will result in current week values and also a DAX for previous week so I can do a comparison.

I have tried a few things but somehow the moment I want to filter by state everything gets jumbled up. Could someone please assist? Thank you!

This is what is happening:

These are the codes I have tried:

Re: DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

Anonymous — Sat, 15 Aug 2020 15:46:25 GMT

Hi cuohanele ,

Please check this blog

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Objective: We would like to have WTD(Week Till Date) Last WTD and This Week vs Last Week.

Dataset: We have taken Sales data. The data is from April 2018 till April 2020. We have created a date Calendar.

Pbix is attached to the blog.

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Re: DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

Greg_Deckler — Sun, 16 Aug 2020 17:36:03 GMT

cuohanele - You might try implementing a sequential week number to make things easier.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116

Also, these might help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/m-p/391487#M123

https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293#M120

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128

Re: DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

amitchandak — Mon, 17 Aug 2020 03:53:59 GMT

cuohanele , refer how to create week wise calendar

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

Work on Week Vs Week using Rank

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Example

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense) This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]))) Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1)) Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

Anonymous — Tue, 18 Aug 2020 07:25:05 GMT

You get wrong results because you are creating a filter over the entire 'LSAR Table'. Filtering in DAX by putting an entire table as a filter is rarely a good idea, and mostly a very bad one for several reasons. Your filter must be put on the Date table in your model and if you do that everything will be working fine. Remember this golden rule of DAX programming: Never filter a table if you can filter a column. The importance of this can't be stressed enough.

Re: DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

Anonymous — Tue, 18 Aug 2020 07:39:42 GMT

// Please note that Date should be // the date table in your model marked // as such and joined to your fact table(s) // on the Date field. This table should have // fields to move through a fiscal calendar. // There should be a field in the date table // called FiscalWeekId storing consecutive(!) // integers that would number all the fiscal // weeks. This column would, of course, be // hidden as it's only an auxiliary one for // doing calculations. // Here's what your measure would look like: [Last Week's LSAR] = var __onlyOneWeekIsVisible = HASONEVALUE( 'Date'[FiscalWeekID] ) var __fiscalWeek = SELECTEDVALUE( 'Date'[FiscalWeekID] ) var __result = If( __onlyOneWeekIsVisible, CALCULATE( [LSAR], 'Date'[FiscalWeekID] = __fiscalWeekID - 1 ALL( 'Date' ) ) ) return __result // This measure colud be written in a shorter // form at the price of readability (but it // might be faster): [Last Week's LSAR] = var __fiscalWeek = SELECTEDVALUE( // Let's say that these ID's start // do not start at -100 in your table. // Bear in mind that unknown weeks // would be marked in a such a table customarily // as having an ID of -1. 'Date'[FiscalWeekID], -10 ) var __result = CALCULATE( [LSAR], 'Date'[FiscalWeekID] = __fiscalWeekID - 1 ALL( 'Date' ) ) return __result

Re: DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

bhawanar — Thu, 17 Nov 2022 04:59:43 GMT

OMG thanks for this advise- extremely helpful!

Re: DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

Anupa_Jayakody — Fri, 03 Feb 2023 21:01:55 GMT

Hi, I am trying to get the week on week difference, but nothing works, here a few options I tried, can anyone help me to fix this !! Great help as I am stuck

1-

Measure =

VAR CurrentWeek= SELECTEDVALUE('Date'[Week number])

VAR CurrentYear= SELECTEDVALUE('Date'[Year])

VAR MaxWeekNumber= CALCULATE(SELECTEDVALUE('Date'[Week number]), ALL('Date'[Date]))

RETURN

SUMX(

FILTER('Date',CurrentWeek-1), [$ value])

New measure =

VAR current_week= SELECTEDVALUE('Date'[Week number])

3-

var LW2 =

var swv = CALCULATE(SUMX('Material trans','Material trans'[Value $]),FILTER(ALL('Date'),'Date'[Week number]= SELECTEDVALUE('Date'[Week number])))

var pwv = CALCULATE(SUMX('Material trans','Material trans'[Value $]),FILTER(ALL('Date'),'Date'[Week number]= SELECTEDVALUE('Date'[Week number])-1))

var selectedWeekMinusPreviousweek = swv - pwv

return pwv

4-

WOW Measure =

VAR CurrentWeek = MAX('Date'[Week number])

VAR PreviousWeek = CurrentWeek-1

RETURN SUMX('Material trans', [$ value]) - SUMX(FILTER('Material trans', 'Material trans'[Week num] = PreviousWeek), [$ value])

VAR current_year= SELECTEDVALUE('Date'[Year])

VAR MaxWeekNumber= CALCULATE(MAX('Date'[Week number]), ALL('Date'))

RETURN

SUMX(FILTER(ALL('Date'),

IF( current_week=1,

'Date'[Week number]= MaxWeekNumber && 'Date'[Year]= current_year-1,

'Date'[Week number]= current_week-1 &&'Date'[Year]= current_year)),

[$ value])