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