Sales last year, same weekday aggregation problem

[Anonymous]

Hello,

I have a measure that gets the sales from last year same weekday which works fine when it is per date, however if I aggregate over the whole 2018, it will be wrong due to my condition.

Here is the formula:

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

The problem is that the total does not work because, I think when it is "aggregated" over several days, the condition in red is always respected. So I would like to find a way to say that this is a condition per day. I have tried doing some sumx but did not work.

Note: DWY is "DWY = ([WeekDay]*100+[Weeks])*10000+[Year]"

if anyone has an idea,... 🙂

[Floriankx]

Hello,

Sales Last Year Same weekday =
Your formula seems a little complicated.

I don't understand your SUM in 'SUM('Sales & Tickets'[Sales]) > 700'.

Do you have several values per day?

Maybe you can provide us some sample data to understand your problem better.

[Anonymous]

Hello,

Thank you for the reply.

I have several stores so yes I have several values (sales) per day.

Regarding the complexity of the formula, it comes from this post and was the only solution I found:

https://community.powerbi.com/t5/Desktop/Weekday-Last-Year-Comparison/td-p/54561

For the 700 condition, I want the sales of last year to not be taken into account if current sules are lower or equal to 700 which explains the ">700" condition.

[Floriankx]

I think as well the SUM('Sales & Tickets'[Sales]) > 700 causes the problem.

I haven't built a table to check but maybe you can use instead

Calculate(SUM('Sales & Tickets'[Sales]);Filter('Sales & Tickets';'Sales & Tickets'[Date]=Earlier('Sales & Tickets').

I know the formula know doesn't get less complicated.

I think there should also be the possibility of Group By but I'm not familiar so this solution I have to leave to others.

Maybe my idea works.