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
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.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello v-yanjiang-msft , 

         

        My report is on direct query, it doesn't accept SUMX