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.

 

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,... :)

 

 

Need Help

3 Replies

  • 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.