Create column that shows data difference compared with last week in date format (YYYYWK)

[Anonymous]

Hey fellow Power BIers

I have loads of data categorized into Year and week (YYYYWK) format. For example this week is 47 and the year 2022 hence data from this week have date 202247. 

I wish to create a column in my matrix that displays the difference compared with last weeks (202246) data. Please see screenshot below on my current table. To the furthest right I wish to add Stock weeks difference which would show +0,7 compared with last week so I can track the development of stock buildup.

This I can achieve in Excel by using Value Field Settings. See desired result in screenshot below and highlighted in yellow

Any ideas on how to achieve this in Power BI? The date (YYYYWK) is currently stored as whole number in my data set. Ideally I would not want to change this too much

PS. For the curious one, this is how I solved it in Excel. Is it possible to perhaps retrieve the DAX formula behind it in Excel and paste into Power BI?

All help is greatly appreciated, thanks
/Sammy

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Please try formula like:

diff =
VAR _previousweek =
    CALCULATE (
        MAX ( 'Table'[Week] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] < MAX ( 'Table'[Week] ) )
    )
VAR _previousvalue =
    CALCULATE (
        [Stock weeks(FC)],
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] = _previousweek )
    )
RETURN
    [Stock weeks(FC)] - _previousvalue

'Table'[Week] (YYYYWK) need to be adjusted to 'whole number' type.

Best Regards,
Community Support Team _ Eason

[TonyZhou1980 · Accepted solution]

Hi Sammy,

Please try this measure and see if it works.

Diff Stock =

 var _previousweek =

    calculate(

        max(Stock[Week]),

        filter(all('Stock'),Stock[Week]<max(Stock[Week]))

    )

var _previousweekqty =

    calculate(

        sum(Stock[Stock Qty]),

        filter(all(Stock),Stock[Week]=_previousweek)

    )

var _previoussubqty =

    calculate(

        sum(Stock[Stock Qty]),

        filter(all(Stock),Stock[Week]=_previousweek),

        values(Stock[Sub-C])

    )

return

    SWITCH(

        TRUE(),

        ISINSCOPE(Stock[Sub-C]),sum(Stock[Stock Qty])-_previoussubqty,

        ISINSCOPE(Stock[Week]),sum(Stock[Stock Qty])-_previousweekqty,

        blank()

    )

[Anonymous · Accepted solution]

@TonyZhou1980 
Ive done some tweaking and following code seems to work to branch down into one sub-category:

Diff. StockWk (FC) =
  var _previousweek =
  calculate(
      max('Power BI'[Week]),
      filter(allselected('Power BI'), 'Power BI'[Week]<max('Power BI'[Week]))
)
  var _previousWEEKqty =
  calculate(
      [Stock weeks (FC)],
      filter(allselected('Power BI'), 'Power BI'[Week] =_previousweek)
)
var _previousPRODqty =
  calculate(
      [Stock weeks (FC)],
      filter(allselected('Power BI'), 'Power BI'[Week]=_previousweek),
      values('Power BI'[Product type])
)
return
  SWITCH(
      TRUE(),
      ISINSCOPE('Power BI'[Product type]),[Stock weeks (FC)]-_previousPRODqty,

      ISINSCOPE('Power BI'[Week]),[Stock weeks (FC)]-_previousWEEKqty,
      blank()
)

I cannot seem to figure out how to add more subcategories to this. Because in reality I would want to branch down further 2-3 subcategories.

Any help is appreciated
/Sammy

[Anonymous · Accepted solution]

Hey,

I have managed now. Many thanks all for the help. If anyone is interested, here is the code I used with help from @TonyZhou1980. Just replace SUB-C with your own sub categories:

var _previousweek =

    CALCULATE(

        MAX('Power BI'[Week]),

        FILTER(ALLSELECTED('Power BI'), 'Power BI'[Week] < MAX('Power BI'[Week]))

    )

var _previousWEEKqty =

    CALCULATE(

        [Stock weeks (FC)],

        FILTER(ALLSELECTED('Power BI'), 'Power BI'[Week] = _previousweek)

    )

var _previousPRODqty =

    CALCULATE(

        [Stock weeks (FC)],

        FILTER(ALLSELECTED('Power BI'), 'Power BI'[Week] = _previousweek),

        VALUES('Power BI'[Sub-C1])

    )

var _previousREGqty =

    CALCULATE(

        [Stock weeks (FC)],

        FILTER(ALLSELECTED('Power BI'), 'Power BI'[Week] = _previousweek),

        VALUES('Power BI'[Sub-C1]),

        VALUES('Power BI'[Sub-C2])

    )

var _previousSUBREGqty =

    CALCULATE(

        [Stock weeks (FC)],

        FILTER(ALLSELECTED('Power BI'), 'Power BI'[Week] = _previousweek),

        VALUES('Power BI'[Sub-C1]),

        VALUES('Power BI'[Sub-C2]),

        VALUES('Power BI'[Sub-C3])

    )

var _previousARTqty =

    CALCULATE(

        [Stock weeks (FC)],

        FILTER(ALLSELECTED('Power BI'), 'Power BI'[Week] = _previousweek),

        VALUES('Power BI'[Sub-C1]),

        VALUES('Power BI'[Sub-C2]),

        VALUES('Power BI'[Sub-C3]),

        VALUES('Power BI'[Sub-C4])

    )

RETURN

    SWITCH(

        TRUE(),

        ISINSCOPE('Power BI'[Sub-C4]), [Stock weeks (FC)] - _previousARTqty,

        ISINSCOPE('Power BI'[Sub-C3]), [Stock weeks (FC)] - _previousSUBREGqty,

        ISINSCOPE('Power BI'[Sub-C2]), [Stock weeks (FC)] - _previousREGqty,

        ISINSCOPE('Power BI'[Sub-C1]), [Stock weeks (FC)] - _previousPRODqty,

        ISINSCOPE('Power BI'[Week]), [Stock weeks (FC)] - _previousWEEKqty,

        BLANK()

    )

[TonyZhou1980 · Accepted solution]

Hi Sammy,

Please try this measure and see if it works.

Diff Stock =

 var _previousweek =

    calculate(

        max(Stock[Week]),

        filter(all('Stock'),Stock[Week]<max(Stock[Week]))

    )

var _previousweekqty =

    calculate(

        sum(Stock[Stock Qty]),

        filter(all(Stock),Stock[Week]=_previousweek)

    )

var _previoussubqty =

    calculate(

        sum(Stock[Stock Qty]),

        filter(all(Stock),Stock[Week]=_previousweek),

        values(Stock[Sub-C])

    )

return

    SWITCH(

        TRUE(),

        ISINSCOPE(Stock[Sub-C]),sum(Stock[Stock Qty])-_previoussubqty,

        ISINSCOPE(Stock[Week]),sum(Stock[Stock Qty])-_previousweekqty,

        blank()

    )

[Anonymous]

Hey @TonyZhou1980 

Big thanks for reaching out.
I am trying to implement your suggestion but it is complaining on:
 "values(Stock[Sub-C])"

What would Sub-C be? It is not declared as a variable prior. Do you know how to sort this out?

Thanks and take care

/Sammy

[TonyZhou1980]

Hi Sammy,

The [Sub-C] is the Subcategory, you can replace it with yours. Let me know if it works.

Thank you

[Anonymous]

Hey again @TonyZhou1980 
Hope your weekend was good and many thanks for trying to help me here

I have 2 issues as of now:

1. sum(Stock[Stock Qty]) is not a valid expression as my [Stock Qty] is already a current measure, it is not part of original data in sheet. Code complains with error msg "Parameter is not the correct type". I cannot use SUM function on it
2. The (Stock[Sub-C]) in my case is (Stock[Product Type]). And whenI put function VALUES in front of it I get error msg "Unexpected expression"

Do you possibly know how to resolve this?

Many thanks
/Sammy

[Anonymous · Accepted solution]

@TonyZhou1980 
Ive done some tweaking and following code seems to work to branch down into one sub-category:

Diff. StockWk (FC) =
  var _previousweek =
  calculate(
      max('Power BI'[Week]),
      filter(allselected('Power BI'), 'Power BI'[Week]<max('Power BI'[Week]))
)
  var _previousWEEKqty =
  calculate(
      [Stock weeks (FC)],
      filter(allselected('Power BI'), 'Power BI'[Week] =_previousweek)
)
var _previousPRODqty =
  calculate(
      [Stock weeks (FC)],
      filter(allselected('Power BI'), 'Power BI'[Week]=_previousweek),
      values('Power BI'[Product type])
)
return
  SWITCH(
      TRUE(),
      ISINSCOPE('Power BI'[Product type]),[Stock weeks (FC)]-_previousPRODqty,

      ISINSCOPE('Power BI'[Week]),[Stock weeks (FC)]-_previousWEEKqty,
      blank()
)

I cannot seem to figure out how to add more subcategories to this. Because in reality I would want to branch down further 2-3 subcategories.

Any help is appreciated
/Sammy

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Please try formula like:

diff =
VAR _previousweek =
    CALCULATE (
        MAX ( 'Table'[Week] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] < MAX ( 'Table'[Week] ) )
    )
VAR _previousvalue =
    CALCULATE (
        [Stock weeks(FC)],
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] = _previousweek )
    )
RETURN
    [Stock weeks(FC)] - _previousvalue

'Table'[Week] (YYYYWK) need to be adjusted to 'whole number' type.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hey, 
Thanks for reaching out
I will try this when I come home, this looks good, no issues adjusting YYYYWK to whole number in the data sheet.
Where can I enter this code though? Will it suffice creating a new measure and simply inserting the code there?

Best wishes

Sammy

[v-easonf-msft]

Hi, @Anonymous 

Yes, it is a measure.

You need to insert the code and put it in your matrix.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hey again @v-easonf-msft 

Many many thanks for providing this code to me, it is very helpful

It seems to work on a total lvl in my powerBI matrix but when I further drill down into subcategories under each week (on product type lvl) it seems not to work... Let me provide you with a screenshot (highlighted in yellow) what I mean:

You can see that on total lvl (in bold font) we have decreased the stockweeks with -0,1 which is correct.

But when I drill down one more "branch" into the matrix we can see that for product type "seat shell junior chair" we decreased with -2,2 stock weeks compared with wk202245 and this is not correct as it should be 24,2-25,1 = -0,9 

Best wishes

Sammy

[amitchandak]

@Anonymous , create a new table with distinct([Year Week]) , say date

create a column - rank on year week

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

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

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

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