Forum Discussion

MHAO's avatar
MHAO
Frequent Visitor
3 years ago
Solved

Measure returns multiple values instead of a single value.

Hi Everyone,

I am trying to pull out the current week-period from a table which have weekly periods named as weekend. i.e. 07/22/23 | 7/29/23 ... and so on.

The measure I work is like 

CurrentWeek = CALCULATE(FILTERS(Table1[Weekend Date]), YEAR(Table1[Weekend Date]) = YEAR(TODAY()) && MONTH(Table1[Weekend Date]) = MONTH(TODAY()) && DAY(Table1[Weekend Date]) - DAY(TODAY()) < 7)

But it returns multiple values. Focusing on the "...(FILTERS(Table1..." part to solve the issue. Any help/comment please?

Thank you

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi MHAO ,

    FILTERS function cannot be used in the foramt "calculate(filters(......". According to your description, if you want to get a table showing the current week-period, here's my solution.

    1.Create a table with below formula:

    CurrentWeek =
SELECTCOLUMNS (
    FILTER (
        'Table1',
        YEAR ( Table1[Weekend Date] ) = YEAR ( TODAY () )
            && MONTH ( Table1[Weekend Date] ) = MONTH ( TODAY () )
            && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) < 7
            && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) > 0
    ),
    "Weekend Date", 'Table1'[Weekend Date]
)

    Result:

     

    Or if you want to use a measure to show the values, create a measure:

    Measure =
VAR _T =
    SELECTCOLUMNS (
        FILTER (
            'Table1',
            YEAR ( Table1[Weekend Date] ) = YEAR ( TODAY () )
                && MONTH ( Table1[Weekend Date] ) = MONTH ( TODAY () )
                && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) < 7
                && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) > 0
        ),
        "Weekend Date", 'Table1'[Weekend Date]
    )
RETURN
    CONCATENATEX ( _T, [Weekend Date], " | " )

    Get the result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi MHAO ,

    FILTERS function cannot be used in the foramt "calculate(filters(......". According to your description, if you want to get a table showing the current week-period, here's my solution.

    1.Create a table with below formula:

    CurrentWeek =
SELECTCOLUMNS (
    FILTER (
        'Table1',
        YEAR ( Table1[Weekend Date] ) = YEAR ( TODAY () )
            && MONTH ( Table1[Weekend Date] ) = MONTH ( TODAY () )
            && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) < 7
            && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) > 0
    ),
    "Weekend Date", 'Table1'[Weekend Date]
)

    Result:

     

    Or if you want to use a measure to show the values, create a measure:

    Measure =
VAR _T =
    SELECTCOLUMNS (
        FILTER (
            'Table1',
            YEAR ( Table1[Weekend Date] ) = YEAR ( TODAY () )
                && MONTH ( Table1[Weekend Date] ) = MONTH ( TODAY () )
                && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) < 7
                && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) > 0
        ),
        "Weekend Date", 'Table1'[Weekend Date]
    )
RETURN
    CONCATENATEX ( _T, [Weekend Date], " | " )

    Get the result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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