Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I
Helper I


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: Screenshot (26).pngScreenshot (27).png



These are the codes I have tried: 

Screenshot (23).pngScreenshot (24).png


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


Measure =
  VAR CurrentWeek= SELECTEDVALUE('Date'[Week number])
  VAR CurrentYear= SELECTEDVALUE('Date'[Year])
  VAR MaxWeekNumber= CALCULATE(SELECTEDVALUE('Date'[Week number]), ALL('Date'[Date]))

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


New measure =

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

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

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'))

IF( current_week=1,
 'Date'[Week number]= MaxWeekNumber && 'Date'[Year]= current_year-1,
 'Date'[Week number]= current_week-1 &&'Date'[Year]= current_year)),
 [$ value])
Not applicable

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.

OMG thanks for this advise- extremely helpful!

Not applicable

// 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,
            'Date'[FiscalWeekID] = __fiscalWeekID - 1
            ALL( 'Date' )
// 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.
var __result =
        'Date'[FiscalWeekID] = __fiscalWeekID - 1
        ALL( 'Date' )
Super User
Super User

@cuohanele , refer how to create week wise calendar


Work on Week Vs Week using Rank



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 :

See if my webinar on Time Intelligence can help:

Appreciate your Kudos.

Super User
Super User

Community Champion
Community Champion

Hi @cuohanele ,


Please check this blog


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.


Harsh Nathani

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


Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.