topic Re: sales of last week in DAX Commands and Tips

sales of last week

Anonymous — Tue, 18 Jun 2024 10:27:30 GMT

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

Re: sales of last week

rajendraongole1 — Tue, 18 Jun 2024 11:07:02 GMT

Hi Anonymous -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!!

Re: sales of last week

Anonymous — Tue, 18 Jun 2024 11:11:35 GMT

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

Re: sales of last week

rajendraongole1 — Tue, 18 Jun 2024 11:17:10 GMT

Hi Anonymous - 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!!

Re: sales of last week

Anonymous — Tue, 18 Jun 2024 11:27:02 GMT

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

Re: sales of last week

Anonymous — Wed, 19 Jun 2024 02:12:34 GMT

Hi Anonymous ,

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.

Re: sales of last week

Anonymous — Wed, 26 Jun 2024 07:00:32 GMT

Hi Anonymous ,

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.