Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

yearly sales difference

hello guys.
i want to get the sales difference between years for that day of the week for that week in particularly.

for ex. for P1W1 Tuesday sales for 2022 is 1813 and and for year 2023 is 1913 so the difference of sales would be 100. similary for wed, and all the days of all the weeks. i want a generalized formula so that it can be applied for all future years also.. i mean if i add up data for 2020 and 2021 then i can check difference of these years or if i want i can check it for year 2020 and 2023 also....very flexible formula.
Thank you

Need Help

8 Replies

  • zenisekd's avatar
    zenisekd
    Super User
    2 years ago

    I would suggest you create (calculate) a reference column +1 year, based on which you self merge the table. 
    This way for one line you would get the actual value and the -1 year value and then you do the calculation you need.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Please try this way.
    Here is my sample dataset:

    I create three slicers:

    Use these three DAXs to create three measures to calculate:

    HWS_CurrectYear = 
VAR C_Year = SELECTEDVALUE('Table'[Year])
VAR C_Period = SELECTEDVALUE('Table'[Period])
VAR C_Day = SELECTEDVALUE('Table'[Day])
RETURN
CALCULATE(
    MAX('Table'[HWS]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = C_Year && 'Table'[Period] = C_Period && 'Table'[Day] = C_Day
    )
)
    HWS_NextYear = 
VAR C_Year = SELECTEDVALUE('Table'[Year])
VAR C_Period = SELECTEDVALUE('Table'[Period])
VAR C_Day = SELECTEDVALUE('Table'[Day])
VAR Value_HWS = 
CALCULATE(
    MAX('Table'[HWS]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = C_Year + 1 && 'Table'[Period] = C_Period && 'Table'[Day] = C_Day
    )
)
RETURN
IF(
    ISBLANK(C_Year + 1),
    0,
    Value_HWS
)
    DIFFERENCE = 'Table'[HWS_NextYear] - 'Table'[HWS_CurrectYear]

    The results are shown 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      hello sir, thank you for ur solution but my requirement is to show in a table not in card. omewhat like this format. here each row is period and week..i.e p1w1,p1w2,p1w3

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ，

        I'm sorry I don't quite understand what results you need to get? I don't quite understand the graph you gave me,

        could you please show your desired results in excel or some other form?
        For example, what kind of result should he get from the sample dataset I used, according to your expectations?

        Best Regards,
        Dino Tao