Forum Discussion

daniel_kusmider's avatar
daniel_kusmider
New Member
4 years ago
Solved

Calculating difference to previous row

Hi,   I'm trying to duplicate in Power BI calculation that's easly done in Excel in a Pivot Table - % diff to previous visible row. In a Pivot Table you can just choose Show Values as % Difference...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, daniel_kusmider 

    Try to create measures like below:

    SUM = SUM('Table'[Leads])
    ThisWeek = 
CALCULATE(
    [SUM],
    FILTER(
        ALLSELECTED('Table'),
        'Table'[End of Week]=MAX('Table'[End of Week])
    )
)
    LastWeek = 
CALCULATE(
    [SUM],
    FILTER(
        ALLSELECTED('Table'),
        'Table'[End of Week]=MAX('Table'[End of Week])-7
    )
)
    LeadDiff% = 
DIVIDE(
    [ThisWeek]-[LastWeek],
    [LastWeek]
)

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

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

amitchandak's avatar
amitchandak
Super User
4 years ago

daniel_kusmider , You need to create a date table with weekend date and try measure like given below .

 

new columns  in date /week table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

 

 

measures
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))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

  • daniel_kusmider's avatar
    daniel_kusmider
    New Member
    4 years ago

    Thanks amitchandak,

     

    That's a step forward 😉

    To what you said I added one measure:

    % Leads Diff = IFERROR((Leads[This Week] - Leads[Last Week])/Leads[Last Week], blank())
    and this one I showed in a table next to summarized Leads quantity:

     

    Now the question is how to make it interactive with slicers filtering the table.

    As you can see below when I use any filters/slicers percentage values make no sence because they are calculated based on a total value for that week:

     


     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      Share the link from where i can download your PBI file.  Clearly show the problem there.