Forum Discussion

MikkoL's avatar
MikkoL
Frequent Visitor
1 year ago
Solved

Week needs data from previous week - problem with filter context?

Hello,

I need to fetch last weeks Sales Orders (SO) in order to calculate current week correctly. I have managed to get last week SOs in cases when I have a SO for an item in the current week, but if there is no SO for a particular item in current week no SO data from last week is fetched😅 I use a MS timeline to select the weeks.

 

For me this feels like I don't access the last week items in the selected weeks context.... (even I can access last weeks SOs)

Here is one version of a measure (note that I need to bind the SO table item to part table in order to get item level data):

PWeekSOs =
VAR LW = WEEKNUM(AVERAGE(OpenSalesRels[ReqDate])) - 1
return
CALCULATE([SOReq Qty],  
    FILTER(ALL(OpenSalesRels),WEEKNUM(OpenSalesRels[ReqDate])=LW &&  OpenSalesRels[CompPart] = MIN(Part[CompPart]))
)
 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,

    Thanks for the solution bhanu_gautam  offered, and i want to offer some more information for user to refer to.

    hello MikkoL , you can refer to the following sample.

    Sample data 

    Sample  measure

    Sum = SUM('Table'[Column2])

    Create a new table

    Table 2 = SUMMARIZE('Table',[Year],[Weeknum],[Year-Week])

    Then create the following measures.

    Currentsales =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Column2] ),
        FILTER ( 'Table', 'Table'[Year-Week] IN VALUES ( 'Table 2'[Year-Week] ) )
    )
RETURN
    a

    LastSales =
CALCULATE (
    [Sum],
    FILTER (
        'Table',
        [Weeknum]
            = SELECTEDVALUE ( 'Table 2'[Weeknum] ) - 1
            && 'Table'[Year] IN VALUES ( 'Table 2'[Year] )
    )
)

    Then put the following field to the matrix.

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • MikkoL's avatar
    MikkoL
    1 year ago

    Thank you Anonymous and bhanu_gautam for your great help.
    Actually here is a solution that works and it is a combination from both of your answers:
    BR, Mikko

    PWeekSOs2 =
      CALCULATE (
        OpenSalesRels[SOReq Qty],
        FILTER (
            ALL(OpenSalesRels),
            OpenSalesRels[Weeknum] = SELECTEDVALUE ( FilterDates[Weeknum] ) - 1
            && OpenSalesRels[Year] = SELECTEDVALUE( ( FilterDates[Year] )
        )
    ),
    VALUES(Part[CompPart])
    )
    Preview
     
     
     

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    MikkoL , Try using

    DAX
    PWeekSOs =
    VAR CurrentWeek = WEEKNUM(AVERAGE(OpenSalesRels[ReqDate]))
    VAR LastWeek = CurrentWeek - 1
    RETURN
    CALCULATE(
    [SOReq Qty],
    FILTER(
    ALL(OpenSalesRels),
    WEEKNUM(OpenSalesRels[ReqDate]) = LastWeek
    ),
    VALUES(Part[CompPart])
    )

    • MikkoL's avatar
      MikkoL
      Frequent Visitor
      1 year ago

      Hello Bhanu,

      That was super fast reply👍thank you! Unfortunately I got the same answer as with my (now yours is named PWeekSOs4):

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

    Thanks for the solution bhanu_gautam  offered, and i want to offer some more information for user to refer to.

    hello MikkoL , you can refer to the following sample.

    Sample data 

    Sample  measure

    Sum = SUM('Table'[Column2])

    Create a new table

    Table 2 = SUMMARIZE('Table',[Year],[Weeknum],[Year-Week])

    Then create the following measures.

    Currentsales =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Column2] ),
        FILTER ( 'Table', 'Table'[Year-Week] IN VALUES ( 'Table 2'[Year-Week] ) )
    )
RETURN
    a

    LastSales =
CALCULATE (
    [Sum],
    FILTER (
        'Table',
        [Weeknum]
            = SELECTEDVALUE ( 'Table 2'[Weeknum] ) - 1
            && 'Table'[Year] IN VALUES ( 'Table 2'[Year] )
    )
)

    Then put the following field to the matrix.

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • MikkoL's avatar
      MikkoL
      Frequent Visitor
      1 year ago

      Thank you Anonymous and bhanu_gautam for your great help.
      Actually here is a solution that works and it is a combination from both of your answers:
      BR, Mikko

      PWeekSOs2 =
        CALCULATE (
          OpenSalesRels[SOReq Qty],
          FILTER (
              ALL(OpenSalesRels),
              OpenSalesRels[Weeknum] = SELECTEDVALUE ( FilterDates[Weeknum] ) - 1
              && OpenSalesRels[Year] = SELECTEDVALUE( ( FilterDates[Year] )
          )
      ),
      VALUES(Part[CompPart])
      )
      Preview