Natural Sales Last Year (same day of the week) if current sales >700

[Anonymous]

Hello everyone,

I have a formula to compute last year sales (same weekday so for example for the 1st of January 2018, you get sales from the 2nd of January 2017) which works great:

Sales Last Year Same weekday =
SUMX (
VALUES ( 'Calendar'[DWY] ),
CALCULATE (
SUM ( 'Sales & Tickets'[Sales] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[DWY] = EARLIER ( Calendar[DWY] ) - 1 )
)
)

I want add a condition that only allows to compute sales last year if the sales of the day are >700:

FILTER ( ALLSELECTED('Calendar'), SUM ( 'Sales & Tickets'[Sales] ) > 700 )

so which gives as a complete formula:

Sales Last Year Same weekday = 
SUMX (
VALUES ( 'Calendar'[DWY] ),
CALCULATE (
SUM ( 'Sales & Tickets'[Sales] ),

FILTER ( ALLSELECTED('Calendar'), SUM ( 'Sales & Tickets'[Sales] ) > 700 ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[DWY] = EARLIER ( Calendar[DWY] ) - 1 )
)
)

but it does not work (returns a blank). I also tried the following:

Sales Last Year Same weekday =
IF (
    SUMX (
        VALUES ( 'Calendar'[DWY] ),
        CALCULATE (
            SUM ( 'Sales & Tickets'[Sales] ),
            FILTER (
                ALLSELECTED ( 'Calendar' ),
                'Calendar'[DWY] = EARLIER ( 'Calendar'[DWY] )
            )
        )
    )
        > 700,
    SUMX (
        VALUES ( 'Calendar'[DWY] ),
        CALCULATE (
            SUM ( 'Sales & Tickets'[Sales] ),
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[DWY] = EARLIER ( Calendar[DWY] ) - 1 )
        )
    ),
    BLANK ()
)

but it returns the sales of the full year 2017 instead of Jan-Apr 2017

If anyone has a clue, it would help me quite a bit!

[v-yuta-msft · Accepted solution]

Hi rlebeau,

When column are your slicer based on? You can use 

ALLSELECTED(Table[Column])

instead of 

ALLSELECTED(Table)

and try again.

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft,

Thanks for the reply.

Thank you for your comment I realized I was maybe using the wrong table/column.

I was using dates from the master calendar instead of the ones from my sales table.

Anyway, thank you for your comment it worked.