create new column with current week/date filter

[Nathan___Mox]

Hey all,

I'm trying to create a new column filtered by the current/todays date/week, see below

item	week starting	amount	new column
a	30 april 2023	100	150
a	7 may 2023 (current)	150	150
a	14 may 2023	50	150
a	21 may 2023	200	150
b	30 april 2023	300	500
b	7 may 2023	500	500
b	14 may 2023	200	500
b	21 may 2023	50	500

could i please get some help on this, if you need any more information let me know.

Thanks

Nathan

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Expected result CC =
VAR _year = 2023
VAR _calendar =
    ADDCOLUMNS (
        CALENDAR ( DATE ( _year - 1, 1, 1 ), DATE ( _year + 1, 12, 31 ) ),
        "@wknum", WEEKNUM ( [Date] + 1, 21 )
    )
VAR _calendartable =
    ADDCOLUMNS (
        _calendar,
        "@year",
            IF (
                MONTH ( [Date] ) = 1
                    && [@wknum] > 50,
                YEAR ( [Date] ) - 1,
                YEAR ( [Date] )
            )
    )
VAR _todaywk =
    MINX ( FILTER ( _calendartable, [Date] = TODAY () ), [@wknum] )
VAR _todayyear =
    MINX ( FILTER ( _calendartable, [Date] = TODAY () ), [@year] )
VAR _wkyearstartingdate =
    MINX (
        FILTER ( _calendartable, [@wknum] = _todaywk && [@year] = _todayyear ),
        [Date]
    )
RETURN
    SUMX (
        FILTER (
            Data,
            Data[item] = EARLIER ( Data[item] )
                && Data[week starting] = _wkyearstartingdate
        ),
        Data[amount]
    )

[Nathan___Mox]

This works thank you heaps!! 

Just one question the first line 'Var _year = 2023' does this mean that only dates in 2023 will work for the dax calculation?