topic Meassure that gets previous year values in DAX Commands and Tips

Meassure that gets previous year values

truttafisker — Tue, 20 Aug 2024 12:24:47 GMT

Hi there all

I have this meassure ..

PrevPeriodSammensat = CALCULATE( ROUND(SUMX(MergedInvoices, MergedInvoices[Beløb Uden moms]),2), FILTER( ALL('Date'), 'Date'[Year Number] = SELECTEDVALUE('Date'[Year Number])-1 && WEEKNUM('Date'[Date]) >= MIN('Date'[Weeknumber]) && WEEKNUM('Date'[Date]) <= MAX('Date'[Weeknumber]) ) )

but for some reason it doesn't include the 31/12 (int his case 2021) ..

are there anyone who has an idea why this is ?

Re: Meassure that gets previous year values

Kaviraj11 — Tue, 20 Aug 2024 13:27:40 GMT

Hi,

The Weeknum function can sometimes cause unexpected results around year boundaries.

Try this:

PrevPeriodSammensat =
CALCULATE(
ROUND(SUMX(MergedInvoices, MergedInvoices[Beløb Uden moms]), 2),
FILTER(
ALL('Date'),
'Date'[Year Number] = SELECTEDVALUE('Date'[Year Number]) - 1 &&
(
('Date'[Weeknumber] >= MIN('Date'[Weeknumber]) && 'Date'[Weeknumber] <= 52) ||
('Date'[Weeknumber] <= MAX('Date'[Weeknumber]) && 'Date'[Weeknumber] >= 1)
)
)
)

Re: Meassure that gets previous year values

truttafisker — Wed, 21 Aug 2024 07:09:04 GMT

hi there..
Thx for the possible solution .. and it actually seems to work .. but before i accept it as solution, why the OR operator and not AND ..
And why 52 weeks instead of 53 ??

Re: Meassure that gets previous year values

Kaviraj11 — Wed, 21 Aug 2024 12:26:08 GMT

Hi,

In your measure, you want to include dates that fall within the same week range but in the previous year. Using 'AND' would require both conditions to be true simultaneously, which isn’t possible for week numbers that span across the end of the year. By using 'OR', you ensure that either condition can be true, allowing you to capture the correct range of dates.

The reason for not using week 53 is that not every year has a 53rd week. The ISO week date system, which is commonly used, only includes a 53rd week if the year starts on a Thursday or is a leap year starting on a Wednesday. Including week 53 could lead to inconsistencies or errors in years that only have 52 weeks.