Forum Discussion

Dev_Pat's avatar
Dev_Pat
Frequent Visitor
5 years ago

MDX query for Specific Year

Hi Team, I need a help to develop a MDX query. Below is the detail requirement. Any help much appriciated.   A calculated column, which will give me 2019 volume number to respective month errecpe...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

Hi Dev_Pat 

 

Can you provide the expected output based on your sample table? Currently I don't understand what should be the expected result. Thanks.

 

Regards,
Community Support Team _ Jing

Dev_Pat's avatar
Dev_Pat
Frequent Visitor
5 years ago

Hi v-jingzhang ,

Volume_2019 column is the expected column from first 4 column.

Can we write MDX code to get 5th column? irrespective of year in month column, can we get the number of 2019 in new calcuated column?

Thanks

MarketCategoryMonthVolumeVolume_2019
Market-1Category-11/1/202160.3939.16
Market-2Category-11/1/20200.5939.16
Market-3Category-11/1/201939.1639.16
     
Market-1Category-12/1/202135.1537.10
Market-2Category-12/1/202060.2537.10
Market-3Category-12/1/201937.1037.10
     
Market-1Category-13/1/202176.2055.40
Market-2Category-13/1/202033.5655.40
Market-3Category-13/1/201955.4055.40
  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi Dev_Pat 

     

    I don't know much about MDX. If it's possible to use DAX, you can use below codes:

    Column =
CALCULATE (
    SUM ( 'Table'[Volume] ),
    ALL ( 'Table' ),
    YEAR ( 'Table'[Month] ) = 2019,
    MONTH ( 'Table'[Month] ) = MONTH ( EARLIER ( 'Table'[Month] ) )
)

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    Hi @Dev_Pat 

     

    I don't know much about MDX. If it's possible to use DAX, you can use below codes:

    Column =
CALCULATE (
    SUM ( 'Table'[Volume] ),
    ALL ( 'Table' ),
    YEAR ( 'Table'[Month] ) = 2019,
    MONTH ( 'Table'[Month] ) = MONTH ( EARLIER ( 'Table'[Month] ) )
)

    072304.jpg

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    Hi @Dev_Pat 

     

    I don't know much about MDX. If it's possible to use DAX, you can use below codes:

    Column =
CALCULATE (
    SUM ( 'Table'[Volume] ),
    ALL ( 'Table' ),
    YEAR ( 'Table'[Month] ) = 2019,
    MONTH ( 'Table'[Month] ) = MONTH ( EARLIER ( 'Table'[Month] ) )
)

    072304.jpg

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.