Calculate Sales Net Amt LFL AY

[AndreiK15]

Hi,

I have the following problem. I try to calculate Sales Net Amt LFL AY using the following measure:

VAR vFilterContext =
FLOOR (
    LOG10 (
        SIGN(ISCROSSFILTERED ( 'Merchandise Structure') +
             ISCROSSFILTERED ( 'Articles') + 
             ISCROSSFILTERED ( 'Product Lists') +
             ISCROSSFILTERED ( 'Promo Classification' ) + 
             ISCROSSFILTERED ( 'Sales Classification' )) * 10 + 1
    ),
    1
)
VAR vResult =
SWITCH (
    vFilterContext,
        0, CALCULATE(SUM('POS Sales'[Sales Net Amt]),'POS Sales'[LFL Id] = 1),
        1, CALCULATE(SUM('Sales'[Sales Net Amt]),'Sales'[LFL Id] = 1)
)
RETURN
    vResult

Measure is ok and return what it should return.

The problem is when I add some filters for some refit stores:

This is what I get when I select After Refit using Site concept name:

and this is what I get if I select Before Refit using the same concept name:

Refit Week Desc is calculated as 0 for refit date and +1, 2 after refit and -1,-2 and so on ... before the refit date.

I need to have a value for Sales Net Amt LFL AY and Sales Net Amt LFL LY DoW when I select both, before or after.

This is the measure I'm using for LFL LY DoW

VAR vFilterContext =
FLOOR (
    LOG10 (
        SIGN(ISCROSSFILTERED ( 'Merchandise Structure') +
             ISCROSSFILTERED ( 'Articles') + 
             ISCROSSFILTERED ( 'Product Lists') +
             ISCROSSFILTERED ( 'Promo Classification' ) + 
             ISCROSSFILTERED ( 'Sales Classification' )) * 10 + 1
    ),
    1
)
VAR vResult =
SWITCH (
    vFilterContext,
        0, CALCULATE(SUM('POS Sales'[Sales Net Amt]),'POS Sales'[LFL Id LY DoW] = 1,DATEADD('Calendar'[Calendar Date],-52*7,DAY)),
        1, CALCULATE(SUM('Sales'[Sales Net Amt]),'Sales'[LFL Id LY DoW] = 1,DATEADD('Calendar'[Calendar Date],-52*7,DAY))
)
RETURN
    vResult

This is what LFL ID key show with After Refit selection:

and with Before Refit selection:

To create a new table is not a solution as the pbix is connected to SSAS, so probably there are some filters I missed.

If anyone can provide me with an idea/solution .... please help...

L.E. Refit Week Desc is in Site Dictionaries table and the relationship is with Sales table (many to one using Time Site Key).

Thank you!

[v-shex-msft]

HI @AndreiK15,

I'd like to suggest you use the date function to manually calculate the filter range to replace the time intelligence parts. For the time intelligence functions, they may not able to do complex customization and accurately control the interaction effects with other tables filters.

Measure =
VAR vFilterContext =
    FLOOR (
        LOG10 (
            SIGN (
                ISCROSSFILTERED ( 'Merchandise Structure' ) + ISCROSSFILTERED ( 'Articles' )
                    + ISCROSSFILTERED ( 'Product Lists' )
                    + ISCROSSFILTERED ( 'Promo Classification' )
                    + ISCROSSFILTERED ( 'Sales Classification' )
            ) * 10 + 1
        ),
        1
    )
VAR currDate =
    MAX ( 'Calendar'[Calendar Date] )
VAR vResult =
    SWITCH (
        vFilterContext,
        0,
            CALCULATE (
                SUM ( 'POS Sales'[Sales Net Amt] ),
                FILTER (
                    ALLSELECTED ( 'POS Sales' ),
                    'POS Sales'[LFL Id LY DoW] = 1
                        && 'POS Sales'[Date]
                            = DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 52 * 7 )
                )
            ),
        1,
            CALCULATE (
                SUM ( 'Sales'[Sales Net Amt] ),
                FILTER (
                    ALLSELECTED ( 'Sales' ),
                    'Sales'[LFL Id LY DoW] = 1
                        && 'Sales'
                            = DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 52 * 7 )
                )
            )
    )
RETURN
    vResult

Regards,
Xiaoxin Sheng

[AndreiK15]

Hi,

Thank you for your answer.

I get the following error:

27/09/2021 10:13:55 Query (36, 28) Calculation error in measure 'Articles'[MyMeasure]: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

There are the filters I use for this:

[v-shex-msft]

HI @AndreiK15,

It seems like a typo on my expression(I missed adding date field on the 'sale' table and power bi compare a column with a table), please try to use the below formula if it helps:

Measure =
VAR vFilterContext =
    FLOOR (
        LOG10 (
            SIGN (
                ISCROSSFILTERED ( 'Merchandise Structure' ) + ISCROSSFILTERED ( 'Articles' )
                    + ISCROSSFILTERED ( 'Product Lists' )
                    + ISCROSSFILTERED ( 'Promo Classification' )
                    + ISCROSSFILTERED ( 'Sales Classification' )
            ) * 10 + 1
        ),
        1
    )
VAR currDate =
    MAX ( 'Calendar'[Calendar Date] )
VAR vResult =
    SWITCH (
        vFilterContext,
        0,
            CALCULATE (
                SUM ( 'POS Sales'[Sales Net Amt] ),
                FILTER (
                    ALLSELECTED ( 'POS Sales' ),
                    'POS Sales'[LFL Id LY DoW] = 1
                        && 'POS Sales'[Date]
                            = DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 52 * 7 )
                )
            ),
        1,
            CALCULATE (
                SUM ( 'Sales'[Sales Net Amt] ),
                FILTER (
                    ALLSELECTED ( 'Sales' ),
                    'Sales'[LFL Id LY DoW] = 1
                        && 'Sales'[Date]
                            = DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 52 * 7 )
                )
            )
    )
RETURN
    vResult

Regards,

Xiaoxin Sheng

[AndreiK15]

Hi @v-shex-msft,

Thanks for your replay, but I don't have a Date or a Calendar Date into Sales table... there is one for POS Sales, but there is none for Sales. Your solution might work, but since I don't have a Date column I can't check or confirm. Is there any other solution? I need to test some more....

Thanks one more time for your help.

[v-shex-msft]

HI @AndreiK15,

How did date value interact with your sales table records? If that is the case, you can use the date value filter on that table to extract the list of keys that links to the sales table then you can use 'in' operator enabled filter effects in your expressions.

The IN operator in DAX - SQLBI

If the above not help, can you please share some more detailed information?

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[AndreiK15]

Hi @v-shex-msft ,

Here's the relationships with Site Disctionaries and all sales relationships.

also POS Sales

I will add today a calendar date to sales though and give it a try.

Thanks!

[v-shex-msft]

HI @AndreiK15,

After I checked your snapshot, I found your sales table seems linked with the calendar with 'timekey' values.
For this scenario, you can filter on the calendar first, and extract corresponding time value fields value, and use it as a filter on the sales table.

VAR _list =
    CALCULATETABLE (
        VALUES ( Calendar[Timekey] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date]
                = DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 52 * 7 )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales Net Amt] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[LFL Id LY DoW] = 1
                && 'Sales'[Timekey] IN _list
        )
    )

Regards,

Xiaoxin Sheng

[AndreiK15]

Hi @v-shex-msft

I have added the code you've wrote, but now I get all blanks:

New Measure = 
VAR vFilterContext =
    FLOOR (
        LOG10 (
            SIGN (
                ISCROSSFILTERED ( 'Merchandise Structure' ) + ISCROSSFILTERED ( 'Articles' )
                    + ISCROSSFILTERED ( 'Product Lists' )
                    + ISCROSSFILTERED ( 'Promo Classification' )
                    + ISCROSSFILTERED ( 'Sales Classification' )
            ) * 10 + 1
        ),
        1
    )
VAR currDate =
    MAX ( 'Calendar'[Calendar Date] )
VAR _list =
    CALCULATETABLE (
        VALUES ( Calendar[Time key] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Calendar Date]
                = DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 52 * 7 )
        )
    )
VAR vResult =
    SWITCH (
        vFilterContext,
        0,
            CALCULATE (
                SUM ( 'POS Sales'[Sales Net Amt] ),
                FILTER (
                    ALLSELECTED ( 'POS Sales' ),
                    'POS Sales'[LFL Id LY DoW] = 1
                        && 'POS Sales'[Time Key]
                           IN _list
                )
            ),
        1,
            CALCULATE (
        SUM ( 'Sales'[Sales Net Amt] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[LFL Id LY DoW] = 1
                && 'Sales'[Time key] IN _list
        )
            )
    )
RETURN
    vResult

A Calendar Date column will be added this week into Sales table and I will try that too.

Anyway, is there something I did wrong ?

Thanks!