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

[MikkoL]

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

)

[Anonymous · Accepted solution]

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.

[Anonymous · Accepted solution]

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 · Accepted solution]

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

[bhanu_gautam]

@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]

Hello Bhanu,

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