CODEWITHSUNDEEP
powerbidax
user1693885
user1693885

Reputation: 493

PowerBI/DAX Calculate Dynamic Sum based of specific column

I'm trying to calculate a dynamic sum based on the value of a specific column in DAX/PowerBI:

data as table:

YEAR_CW YEAR_CW_LY AMOUNT_NET
2022-01 2021-01 76.47
2023-01 2022-01 342.53
2023-01 2022-01 325
2023-01 2022-01 111
2023-01 2022-01 629.25
2023-01 2022-01 196.56
2022-01 2021-01 34.2

The aim is to sum AMOUNT_NET for last year's calendar week: enter image description here

so far I have tried:

AMOUNT_NET_LY = CALCULATE(
        SUM([AMOUNT_NET]),
        [YEAR_CW]=[YEAR_CW_LY]
)

which works only if instead of [YEAR_CW_LY], the calendar week is hardcoded (e.g. "2023-01"), but does not work if a dynamic filter has to be applied.

Upvotes: 0

Views: 402

Answers (1)

Dordi
Dordi

Reputation: 778

You can use YEAR_CW as it is:

AMOUNT_NET_LY = CALCULATE(
    SUM([AMOUNT_NET]),
    net[YEAR_CW] = CONCATENATE(LEFT(MAX([YEAR_CW]), 4) - 1,CONCATENATE("-",RIGHT(MAX([YEAR_CW]), 2)))
)

Upvotes: 1

Related Questions