Forum Discussion

pupadhya's avatar
pupadhya
Icon for Helper I rankHelper I
4 years ago
Solved

Week on Week Actual Variance + with Forecast Variance

Week variance

Hello All,

 

I have data which gets updated on weekly basis, So source file has Week column under that every week data gets updated. For Ex: 07 March week(Previous Week) will have some customer revenue and 14 March week (Current Week) will have updated revenue details and these two details under same column under the header of Week, so how do I find the variance between two weeks?

 

When i try to go for measure, it is not recognizing any table so that I can find the variance and additionally once I have the variance for the above two, I need to find the variance between Current Week vs Forecasted revenue for the month (which is constant figure).

Also, in the picture Week 7 & 14 is coming in the same column, how we can show week 7 & 14 in seperate column?

 

Please let me know how I can approach this? Maybe i can share file. Thanks 

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    pupadhya , Create a new table with distinct of this column( If there is year take that)

     

    Distinct(Table[Day])

     

    Or have table with week year

     

    Create a rank of Day oe week year in new table , new column

    Week Rank = RANKX(all('Date'),'Date'[Day],,ASC,Dense)

    or

    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    then try measures  like
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

     

    Power BI — Week on Week and WTD
    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
    https://www.youtube.com/watch?v=pnAesWxYgJ8

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi pupadhya 

    I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

    If you need more help, please let me know.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • PBIdashboards's avatar
    PBIdashboards
    Icon for Post Patron rankPost Patron
    2 months ago

    The issue is that using Week as a text/number column without a proper Date table means DATEADD and PREVIOUSWEEK don't work. The fix is to use FILTER + ALL against the Week column directly:

     
     

    Prev Week Revenue =
    VAR _currentWeek = MAX(Table[Week])
    RETURN
    CALCULATE(
    SUM(Table[Revenue]),
    ALL(Table[Week]),
    Table[Week] = _currentWeek - 1
    )

    WoW Variance =
    DIVIDE([Revenue] - [Prev Week Revenue], ABS([Prev Week Revenue]))

    This bypasses the Date table requirement entirely  works directly with your Week number column.

    For teams where weekly reporting spans multiple metrics (revenue + units + margin), each metric needs this pattern duplicated. Flexa Tables on AppSource handles WoW as a built-in column  select the two weeks to compare directly in the published report