Calculated actual by last week (1-7 = week 1 | 8-14 = week 2 | etc

[Anonymous]

Hello Everyone

hope you stay healthy

I have problem for calculate actual by Last Week,  I don't wanna use weeknum for this case.

I use this week method (Month-Week) for calculated my actual, can I apply dax calculate to find the actual number from last week with my week method?

Month-Week =
VAR _DAY = DAY([Date]) RETURN

SWITCH( TRUE(),
_DAY >= 1 && _DAY <= 7, 'calendar'[bulan3] & " " & "Week 1",
_DAY >= 8 && _DAY <= 14, 'calendar'[bulan3] & " " & "Week 2",
_DAY >= 15 && _DAY <= 21, 'calendar'[bulan3] & " " & "Week 3",
'calendar'[bulan3] & " " & "Week 4"
)

please describe in detail
thank you guys

[v-yiruan-msft · Accepted solution]

Hi  @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

Previous week sales = 
VAR _selyear =
    SELECTEDVALUE ( 'calendar'[Year] )
VAR _selmonth =
    SELECTEDVALUE ( 'calendar'[Month] )
VAR _selweeknum =
    SELECTEDVALUE ( 'calendar'[Weeknum] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Actual] ),
        FILTER (
            ALLSELECTED ( 'calendar' ),
            'calendar'[Year] = _selyear
                && IF (
                    _selweeknum = 1,
                    'calendar'[Month] = _selmonth - 1
                        && 'calendar'[Weeknum] = 4,
                    'calendar'[Month] = _selmonth
                        && 'calendar'[Weeknum] = _selweeknum - 1
                )
        )
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

sorry to late response Ms Ying

thanks for your answer

can you help me I just wanna showing last week actual only.

if actual from 1 June until 19 June, I just wanna see cummulative only week 3 (15 June, 16 June, 17 June, 18 June & 19 June).

[amitchandak]

@Anonymous , I usually prefer to create week rank or Month Week, based on need and can work on this week vs last week

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Month Week No],,ASC,Dense)

Then measures
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))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thanks @amitchandak for your solution

I have trouble for the rank, can you fix this issues?