cancel
Showing results for
Did you mean:
Highlighted
Anonymous
Not applicable

## Sales last year, same weekday aggregation problem

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

3 REPLIES 3
Highlighted
Solution Sage

## Re: Sales last year, same weekday aggregation problem

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.

Highlighted
Anonymous
Not applicable

## Re: Sales last year, same weekday aggregation problem

Hello,

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.

Highlighted
Solution Sage

## Re: Sales last year, same weekday aggregation problem

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021