Forum Discussion

radon's avatar
radon
New Member
2 years ago
Solved

Compare values from prevoius year per category

Hello Community,

 

I have a table with the date, week, sales and country

 

date     I weeknumber I sales    I country

--------I----------------I--------I----------I

1.1.22    1                      1          US

1.1.22    1                      3          CAN

2.1.22    1                      4         IT

3.1.22    1                      5         IT

--------I----------------I--------I----------I

1.1.23    1                       2         IT

1.1.23    1                      10        US

2.1.23    1                      12        US

2.1.23    1                      10        IT

2.1.23    1                      15        CAN

3.1.23    1                       4         CAN

4.1.23    1                       2         IT

4.1.23    1                      10        US

--------I----------------I--------I----------I        

 

Out of this very large, I like to create a "previous year" comparison per week and country.

I created a new table "WeeklySales" and calculated the TotalSalesPerWeek.

 

This worked well and I could create a graph comparing WeeklySales for the same weeks from previous years.

However: in the new table I "lost" the county value.

 

It would be great if someone could help me with the first step of creating a new table with WeeklySales per country like the following:

 

In 2024

week 1 for IT: 14 sales

week 1 for US: 23 sales

...

 

Thanks!

 

 

 

 

 

 

 

 

 

       

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi radon ,

    You don't need to create a new table, just use these DAXs to create two measures:

    CurrentYear = 
CALCULATE(
    SUM(Sheet16[sales]),
    YEAR('Sheet16'[date]) = SELECTEDVALUE(Slicer[Year]) && 'Sheet16'[weeknumber] = SELECTEDVALUE(Sheet16[weeknumber]) && 'Sheet16'[country] = SELECTEDVALUE(Sheet16[country]),
    ALLSELECTED(Sheet16)
)
    PreviousYear = 
CALCULATE(
    SUM(Sheet16[sales]),
    YEAR('Sheet16'[date]) = SELECTEDVALUE(Slicer[Year]) - 1 && 'Sheet16'[weeknumber] = SELECTEDVALUE(Sheet16[weeknumber]) && 'Sheet16'[country] = SELECTEDVALUE(Sheet16[country]),
    ALLSELECTED(Sheet16)
)

    Here is my sample data:

    The final output is below:


     

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • audreygerred's avatar
    audreygerred
    Icon for Super User rankSuper User
    2 years ago

    Hello! You can create a few measures to handle what you need.

    CY = SUM(Table[Sales])

    PY = CALCULATE([CY], SAMEPERIODLASTYEAR(DateTable[Date])

     

    Once you have both of those measures, you can add a slicer to your canvas and set the year to the current year (or put a filter in teh filter pane). In either case, grab the Year from your Date table. Then, create a matrix visual (or table visual) and put Week Number (from date table) in rows, then nest country in under week number and put your measures in Value.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi radon ,

    You don't need to create a new table, just use these DAXs to create two measures:

    CurrentYear = 
CALCULATE(
    SUM(Sheet16[sales]),
    YEAR('Sheet16'[date]) = SELECTEDVALUE(Slicer[Year]) && 'Sheet16'[weeknumber] = SELECTEDVALUE(Sheet16[weeknumber]) && 'Sheet16'[country] = SELECTEDVALUE(Sheet16[country]),
    ALLSELECTED(Sheet16)
)
    PreviousYear = 
CALCULATE(
    SUM(Sheet16[sales]),
    YEAR('Sheet16'[date]) = SELECTEDVALUE(Slicer[Year]) - 1 && 'Sheet16'[weeknumber] = SELECTEDVALUE(Sheet16[weeknumber]) && 'Sheet16'[country] = SELECTEDVALUE(Sheet16[country]),
    ALLSELECTED(Sheet16)
)

    Here is my sample data:

    The final output is below:


     

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.