groupby multiple columns and calculate the difference base on the date

[zakkyang]

Hi,

There are columns like: week number, product, country and sales. I want to group by product and country to calculate the difference between previous week (order by week in the group). Any idea? Thank you in advance.

[v-jingzhang · Accepted solution]

Hi @zakkyang 

If the columns you mentioned are all from the same table, you could create a measure like below to calculate the difference between each week and its previous week. And put product, country, week columns, sum of sales and the Difference measure into a matrix visual to check the differences.

Difference = 
VAR _thisWeekNumber = SELECTEDVALUE('Table (2)'[Week Number])
VAR _thisWeekSales = SUM('Table (2)'[Sales])
VAR _previousWeekSales = CALCULATE(SUM('Table (2)'[Sales]),ALLEXCEPT('Table (2)','Table (2)'[Product],'Table (2)'[Country]),'Table (2)'[Week Number]=_thisWeekNumber-1)
RETURN
IF(ISBLANK(_previousWeekSales),BLANK(),_thisWeekSales - _previousWeekSales)

Download the attached .pbix for details.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Coriel-11]

Hi @v-jingzhang 

Thank you so much much for this. I spent much of yesterday pulling my hair out over one similar to this and couldn't find a similar question in community.

The closest I got was using a measure like this:

diff = 
CALCULATE (
        SUM ( 'Key Webpages'[Pageviews] ), 'Key Webpages'[Months back] 
    )
        - CALCULATE (
            SUM ( 'Key Webpages'[Pageviews] ), 'Key Webpages'[Months back] -1 
        )

I adjusted yours and it worked perfectly! Would you mind telling, is the reason you use variables here (aside from neatness/simplicity) that it enables you to use the SELECTEDVALUE function where it wouldn't otherwise work (i.e. in a T/F expression)?

[zakkyang]

Hi ,

I got lost in your contents. Could you give a direct solution with code? I am new to PowerBI and please allow me to digest through your code directly. Thanks a lot.

[amitchandak]

@zakkyang , Join It with date table and use week number and week rank.

refer my blog for that- 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8