Calculating difference to previous row

[daniel_kusmider]

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.

[v-angzheng-msft · Accepted solution]

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

[daniel_kusmider]

Thanks @v-angzheng-msft,

That's exactly what I needed.

I actualy found a solution on my own on Power Query level but this is way better.

[smpa01]

@daniel_kusmider  provide sample data/pbix?

[amitchandak]

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

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]

Hi,

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