Forum Discussion

mady90's avatar
mady90
Frequent Visitor
7 years ago
Solved

Week over Week change

 

I have 2 tables, a date table that consists of the following fields:date, year, yearmonth, month, day, WeekNum The second table Consists of the following fields: Name, Cost, Date. The tables are linked by the date measure. I want a dax measure that can give me week over week change in cost. I already have a month over month change in cost measure but the same logic can't be applied here because "dateadd" does not give me "Week addition".

Week over Week calculation gives me wrong results.

Week Over Week = CALCULATE(
SUM ( UsageDetails[Cost] )
    - CALCULATE (
        SUM ( UsageDetails[Cost] ),
        FILTER (
            ALL ('Date' ),
            'Date'[Year] = YEAR( NOW() )
                && 'Date'[Week Number]
                    = MAX ( 'Date'[Week Number] ) - 1
                && 'Date'[Week Day] = MAX ( 'Date'[Week Day] )
        )
    ))
Need Help
  • mady90's avatar
    mady90
    7 years ago

    Just resolved the issue. 

    Showing the dax measure for week over week change(In case it helps someone else):

    Week Over Week = 
VAR todaycost =CALCULATE(SUM(UsageDetails[Cost]),DATEADD('Date'[Date],0,DAY))
   
VAR LastWeek =
    CALCULATE (
        SUM ( UsageDetails[Cost] ),
        DATEADD(  'Date'[Date],-7, DAY)
    )
RETURN
   todaycost-LastWeek

2 Replies

  • mady90's avatar
    mady90
    Frequent Visitor
    7 years ago

    Just resolved the issue. 

    Showing the dax measure for week over week change(In case it helps someone else):

    Week Over Week = 
VAR todaycost =CALCULATE(SUM(UsageDetails[Cost]),DATEADD('Date'[Date],0,DAY))
   
VAR LastWeek =
    CALCULATE (
        SUM ( UsageDetails[Cost] ),
        DATEADD(  'Date'[Date],-7, DAY)
    )
RETURN
   todaycost-LastWeek
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Tough to verify without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    But, that being said, I would suggest moving your MAX('Date'[Week Number]) and MAX('Date'[Week Day]) to the beginning of your formula as VAR statements. The likely issue is that since you are calculating those within a block that includes an ALL statement, you are likely getting 52/53 for your Week Number and 6/7 for your Week Day every time. You need to get those outside of an ALL block.