MDX query for Specific Year

[Dev_Pat]

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.

	Volume	2019 Volume
1/1/2020	54.89
2/1/2020	51.64
3/1/2020	98.12
4/1/2020	11.62
5/1/2020	84.98
6/1/2020	87.16
7/1/2020	52.81
8/1/2020	46.45
9/1/2020	48.92
10/1/2020	20.93
11/1/2020	12.60
12/1/2020	31.71
1/1/2021	7.83
2/1/2021	83.78
3/1/2021	73.19
4/1/2021	37.44

Best Regards

Dev

[v-jingzhang]

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]

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

Market	Category	Month	Volume	Volume_2019
Market-1	Category-1	1/1/2021	60.39	39.16
Market-2	Category-1	1/1/2020	0.59	39.16
Market-3	Category-1	1/1/2019	39.16	39.16
Market-1	Category-1	2/1/2021	35.15	37.10
Market-2	Category-1	2/1/2020	60.25	37.10
Market-3	Category-1	2/1/2019	37.10	37.10
Market-1	Category-1	3/1/2021	76.20	55.40
Market-2	Category-1	3/1/2020	33.56	55.40
Market-3	Category-1	3/1/2019	55.40	55.40

[Syndicate_Admin]

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]

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.

[v-jingzhang]

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.