topic Re: Measure doesn't contain one week in DAX Commands and Tips

Measure doesn't contain one week

lucie_rabochova — Mon, 08 Apr 2024 04:11:59 GMT

Hi,

I have a measure calculating previous week sessions and I need to remove week 2022-53 and 2023-53 from this measure, because there is no sessions and it causing me blank space - see print screen.

sessions - previous week = VAR CurrentWeek = SELECTEDVALUE(DimDates[Weeknumber]) VAR CurrentYear = SELECTEDVALUE(DimDates[Year]) VAR MaxWeekNumber = CALCULATE(MAX(DimDates[Weeknumber]),ALL(DimDates)) RETURN SUMX( FILTER( ALL(DimDates), IF(CurrentWeek = 1, DimDates[Weeknumber] = MaxWeekNumber && DimDates[Year] = CurrentYear - 1, DimDates[Weeknumber] = CurrentWeek - 1 && DimDates[Year] = CurrentYear ) ), [LegoSessions(Channel)] )

My goal is:

remove weeks 2022-52 and 2023-53
week 2022-52 will be previous week of 2023-01
week 2023-52 will be previous week of 2024-01

Thank you for your help.

Re: Measure doesn't contain one week

123abc — Mon, 08 Apr 2024 06:36:42 GMT

Here's the modified version of your measure:

RETURN
SUMX(
FILTER(
ALL(DimDates),
IF(
CurrentWeek = 1,
IF(
DimDates[Weeknumber] = MaxWeekNumber && DimDates[Year] = CurrentYear - 1,
TRUE(),
FALSE()
),
IF(
CurrentWeek = 2 && MaxWeekNumber = 52,
DimDates[Weeknumber] = 1 && DimDates[Year] = CurrentYear,
IF(
CurrentWeek = 2 && MaxWeekNumber = 53,
DimDates[Weeknumber] = 1 && DimDates[Year] = CurrentYear + 1,
DimDates[Weeknumber] = CurrentWeek - 1 && DimDates[Year] = CurrentYear
)
)
)
),
[LegoSessions(Channel)]
)

In this modified version:

If the current week is the first week of the year and the maximum week number is 52, it considers the previous week as week 1 of the current year.
If the current week is the first week of the year and the maximum week number is 53, it considers the previous week as week 1 of the next year.
For all other cases, it calculates the previous week normally.

This adjustment should remove the blank spaces caused by weeks 2022-52 and 2023-53 and correctly calculate the previous week sessions as per your requirements

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Measure doesn't contain one week

lucie_rabochova — Tue, 09 Apr 2024 04:44:53 GMT

Hi 123abc,

Thank you for your help, but updated measure doesn't work as I expected. I'm attaching PBIX file :

https://drive.google.com/file/d/1TBLqi8cRoaFFkxVG50C_fjSnwNDaf-MW/view?usp=sharing

Previous week for 2023-01 should be 2022-52 but it's still blank and 53 weeks still showing...

Can you please have a look at the file?

Re: Measure doesn't contain one week

Anonymous — Tue, 09 Apr 2024 06:14:06 GMT

Hi lucie_rabochova ,

You can try using the following dax.

Dax_sessions - previous week = VAR CurrentWeek = SELECTEDVALUE ( DimDates[Weeknumber] ) VAR CurrentYear = SELECTEDVALUE ( DimDates[Year] ) VAR MaxWeekNumber = CALCULATE ( MAX ( DimDates[Weeknumber] ), ALL ( DimDates ) ) RETURN SWITCH ( TRUE (), MAX ( 'DimDates'[Weeknumber] ) = 1, SUMX ( FILTER ( ALL ( 'DimDates' ), 'DimDates'[Year] = MAX ( 'DimDates'[Year] ) - 1 && 'DimDates'[Weeknumber] = MAXX ( FILTER ( ALL ( 'DimDates' ), 'DimDates'[Year] = MAX ( 'DimDates'[Year] ) - 1 ), [Weeknumber] ) - 1 ), [LegoSessions(Channel)] ), CurrentWeek = 2 && MaxWeekNumber = 52, SUMX ( FILTER ( ALL ( 'DimDates' ), DimDates[Weeknumber] = 1 && DimDates[Year] = CurrentYear ), [LegoSessions(Channel)] ), CurrentWeek = 2 && MaxWeekNumber = 53, SUMX ( FILTER ( ALL ( 'DimDates' ), DimDates[Weeknumber] = 1 && DimDates[Year] = CurrentYear + 1 ), [LegoSessions(Channel)] ), MAX ( 'DimDates'[Weeknumber] ) = MAXX ( FILTER ( ALL ( 'DimDates' ), 'DimDates'[Year] = MAX ( 'DimDates'[Year] ) ), [Weeknumber] ), BLANK (), SUMX ( FILTER ( ALL ( 'DimDates' ), DimDates[Weeknumber] = CurrentWeek - 1 && DimDates[Year] = CurrentYear ), [LegoSessions(Channel)] ) )

Best Regards,

Liu Yang

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

Re: Measure doesn't contain one week

lucie_rabochova — Tue, 09 Apr 2024 14:52:41 GMT

Hi Liu Yang,

your measure works perfect, thank you so much, but there is one mistake, see the prinscreen and PBIX file (https://drive.google.com/file/d/1TBLqi8cRoaFFkxVG50C_fjSnwNDaf-MW/view?usp=sharing)

for weeks number 2 (2022-02, 2023-03 and 2024-02) it is showing incorrect values. Can you please fix this?

Re: Measure doesn't contain one week

lucie_rabochova — Fri, 12 Apr 2024 04:52:46 GMT

Any update here please?

Re: Measure doesn't contain one week

Anonymous — Fri, 12 Apr 2024 07:18:16 GMT

Hi lucie_rabochova ,

If week 2 wants to display the values from week 1, you can use the following dax:

Best Regards,

Liu Yang

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