Measure doesn't contain one week

[lucie_rabochova]

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.

[Anonymous · Accepted solution]

Hi  @lucie_rabochova ,

If week 2 wants to display the values from week 1, you can use 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)]),
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.

[Anonymous]

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.

[lucie_rabochova]

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?

[lucie_rabochova]

Any update here please?

[123abc]

Here's the modified version of your measure:

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,
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.

[lucie_rabochova]

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?