cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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,

 

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.

Highlighted
Solution Sage
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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors