DAX for CURRENT WEEK VALUE and PREVIOUS WEEK VALUE

[cuohanele]

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: 

[Anupa_Jayakody]

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]

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.

[bhawanar]

OMG thanks for this advise- extremely helpful!

[Anonymous]

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

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

@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

[harshnathani]

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!