Forum Discussion

Nathan___Mox's avatar
Nathan___Mox
Regular Visitor
3 years ago
Solved

create new column with current week/date filter

Hey all,

 

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

itemweek startingamountnew column
a30 april 2023100150
a7 may 2023 (current)150150
a14 may 202350150
a21 may 2023200150
b30 april 2023300500
b7 may 2023500500
b14 may 2023200500
b21 may 202350500

 

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

 

Thanks

Nathan

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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]
    )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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's avatar
      Nathan___Mox
      Regular Visitor
      3 years ago

      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?