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 from... and choose <previous>.

This calculates in real time as I filter data using Slicers.

I've tried replicating that in Power BI, but I have no clue of how to do it.

 

Data look like this:

I have 4 categories: Leads[Age], Leads[Sex], Leads[Lead Type] and Leads[Product] (in each category there are couple different values possible) and dates in End of Week by which I need to summarise Leads values.

 

In the end I need a table/matrix listed by dates from End of Week.

In the first column I need summarised values of Leads and in the second % difference from previous row and for it to be calculated as somebody filters the report by categories listed above or by date.

 

Here's how the end result looks like in Excel's Pivot Table:

 

First column's easy but if anyone could help with the second I'd realy appreciate it.

 

 

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

6 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    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.