Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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 |
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.