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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cuohanele
Helper I
Helper I

DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

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

7 REPLIES 7

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


2- 

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])
Anonymous
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!

Anonymous
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,
        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
amitchandak
Super User
Super User

@cuohanele , refer how to create week wise calendar

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

 

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-La...

 

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-Y...


Appreciate your Kudos.

Greg_Deckler
Super User
Super User

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
harshnathani
Community Champion
Community Champion

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-La...

 

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors