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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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