Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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

Need Help
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    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's avatar
    TonyZhou1980
    3 years ago

    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's avatar
    Anonymous
    3 years ago

    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's avatar
    Anonymous
    3 years ago

    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()
        )

11 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        v-easonf-msft
        Community Support
        3 years ago

        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

         

  • TonyZhou1980's avatar
    TonyZhou1980
    Resolver I
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        TonyZhou1980
        Resolver I
        3 years ago

        Hi Sammy,

         

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

         

        Thank you

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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()
        )