Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure to extract month

Hello Community,   I am connected to a live dataset where I can't create calculated columns or go to the power query, I have a column date and I want to extract the month/Year from it.  Is there a...
Need Help
v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

As measure calculated by the context, in the formula, the context is date, if you put date in the visual, the formula will return the current date in the same row, if you don't put date in the visual, the formula will return the max date in the table.

My solution is put date in the visual, and create another measure to sum the value.

SUM =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
            && MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
    ),
    'Table'[Value]
)

Get the correct result.

I attach my sample below for 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.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello v-yanjiang-msft 

 

Thanks for your effort. Unfortenatly this is not the expected result, I made some modification on your pbix

file, I added a number column and another date in 2020,

 

what I want to display is a table showing the sum of value per number and date for example:

Number        Date             SUM

1002             FEB 2020      55

1002             FEB 2022      15

1003             FEB 2020      40

1003             FEB 2022      20

 

I hope I was clear 

 

Thanks in advance 🙂

          

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    Does your expected result based on the data in the screen shot? In your screen shot, in my understanding, you want to get:

    Num   Date          Sum

    1002   2022-01        6

    1003   2022-02        15

    1002   2020-01        11

    1003   2020-01        15

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Sorry v-yanjiang-msft , but the measure didn't work because it's direct query 😞

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

        I modify the formula based on your sample data, you can have a try.

        SUM =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
            && MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
            && 'Table'[Number] = MAX ( 'Table'[Number] )
    ),
    'Table'[Value]
)

        Get the result.

        I attach the sample below for 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.