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 errecpective of any year in the row. Below is a sample table.

 Volume2019 Volume
1/1/202054.89 
2/1/202051.64 
3/1/202098.12 
4/1/202011.62 
5/1/202084.98 
6/1/202087.16 
7/1/202052.81 
8/1/202046.45 
9/1/202048.92 
10/1/202020.93 
11/1/202012.60 
12/1/202031.71 
1/1/20217.83 
2/1/202183.78 
3/1/202173.19 
4/1/202137.44 

 

Best Regards

Dev

Need Help

5 Replies

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