sales of last week

[userpien]

 
Hi, I wrote this function to calculate the sales for the current week. 

Sales Current Year = CALCULATE([Sales], FILTER(ALL(Time),Time[Week Rank]=max(Time[Week Rank])))

But I want to know the sold for the week before the current week so I wrote
Sales Past Year = CALCULATE([Sales], FILTER(ALL(Time),Time[Week Rank]=max(Time[Week Rank])-1))

but this doesn't work, beacuse it returns me BLANK. Only if i put -100 it return me something.

Plus, i would like to calculate sales of previous week of past year.

What should i write?

thank you

[v-kaiyue-msft]

Hi @userpien ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards,

Clara Gong

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

[rajendraongole1]

Hi @userpien -Create a measures to avoid issues with context and gaps in the Week Rank, i am using EARLIER function to correctly refer to the previous week.

Sales Previous Week =
CALCULATE(
[Sales],
FILTER(
ALL(Time),
Time[Week Rank] = MAX(Time[Week Rank]) - 1
)
)

create a measure for previous Week Last Year:

Sales Previous Week Last Year =
VAR CurrentWeekRank = MAX(Time[Week Rank])
VAR PreviousWeekRankLastYear =
CALCULATE(
MAX(Time[Week Rank]),
FILTER(
ALL(Time),
Time[Year] = MAX(Time[Year]) - 1 &&
Time[Week Rank] < CurrentWeekRank
)
)
RETURN
CALCULATE(
[Sales],
FILTER(
ALL(Time),
Time[Week Rank] = PreviousWeekRankLastYear &&
Time[Year] = MAX(Time[Year]) - 1
)
)

check the above measure still returns any blanks. Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[userpien]

 where did you use EARLIER? i don't see it, thank you!

[rajendraongole1]

Hi @userpien - The EARLIER function isn't directly used in the above dax funtion but can be helpful in some scenarios for row contect. we can use direct approach with variables to handle context and gaps in the Week Rank effectively.

try with above measure and let me know.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[userpien]

i wrote this function and now it works, for sales past week

Sales Past Week=

VAR CurWeek = WEEKNUM(TODAY(), 21)

VAR CurYear = YEAR(TODAY())

VAR PrevWeek = IF(CurrtWeek = 1, CALCULATE(MAX(Time[number week]), ALL(Time), Time[Year] = CurrentYear - 1), CurrentWeek - 1)

VAR Prev_Year = IF(CurWeek = 1, CurYear - 1, CurYear)

RETURN

SUMX(

    FILTER(

        ALL(Time),

        Time[number week] = PrevWeek && Time[Year] = Prev_Year

    ),

    [Sold]

)

but how i edit this to have same week for past year? Thank you

[v-kaiyue-msft · Accepted solution]

Hi @userpien ,

Thanks for the reply from @rajendraongole1 , please allow me to provide another insight: 

You can try this.

Sales Same Week Last Year =
VAR CurWeek = WEEKNUM(TODAY(), 21)
VAR CurYear = YEAR(TODAY())
VAR SameWeekLastYear = CurWeek
VAR LastYear = CurYear - 1
RETURN
SUMX(
    FILTER(
        ALL(Time),
        Time[number week] = SameWeekLastYear && Time[Year] = LastYear
    ),
    [Sold]
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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