Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.   ...
Need Help
Floriankx's avatar
Floriankx
Solution Sage
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Floriankx
      Solution Sage
      8 years ago

      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.