How to get Data for Year and Year-x

[Breadstone]

Hello,

I have a data table with the following columns:

• KNR = Course # (as Text)
• KW = Calender Week (as Number)
• Year (as Number)
• TN = Number of participants (as Number)

I am trying to show the number of participants for the chosen year and calender week in one column (which works perfectly) and the comparative value for last year and the year before in their own columns.

I ended up with the following:

TN_STFS_VJ =

    CALCULATE(

        SUMX('TN_STFS',[TN]), 'TN_STFS'[Year]-1

    )

The Result is 1k higher than it should be. I tried different routes and have no idea where to go from here.

[Anonymous · Accepted solution]

Hi @Breadstone ，

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Create calculate column
Last year

TN_STFS_VJ = 
CALCULATE(
    SUMX('TN_STFS', 'TN_STFS'[TN]),
    FILTER(
        'TN_STFS',
        'TN_STFS'[Year] = MAX('TN_STFS'[Year]) - 1
    )
)

the year before last

TN_STFS_VJ_2 = 
CALCULATE(
    SUMX('TN_STFS', 'TN_STFS'[TN]),
    FILTER(
        'TN_STFS',
        'TN_STFS'[Year] = MAX('TN_STFS'[Year]) - 2
    )
)

Diffierence

Difference = TN_STFS[TN_STFS_VJ]-TN_STFS[TN_STFS_VJ_2]

Final output

Best regards,

Albert He

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

[lbendlin]

Can you please be more specific.  The WEEKNUM alone has a dozen variations

WEEKNUM function (DAX) - DAX | Microsoft Learn

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...