question

MartinOLoughlin-5927 avatar image
0 Votes"
MartinOLoughlin-5927 asked MartinOLoughlin-5927 answered

SSAS Multidimensional model presented in Power BI

I have a multidimensional model with a number of calendars, order date, ship date, etc and various measures, order qty, ship qty , etc. and present through PowerBI in a single table

Rows would be customers, and the columns would be orders last year to date, order this year to date, variance.

The issue i am having is as follows.

I need to be able to show the various measures aggregated by YTD, LYTD against all the calendars.

What i can do is create a measure for each, so far i have the original measure. i then have a Last year

"order qty"
"order qty LY"
"order qty shipping calendar YTD"
"order qty shipping calendar LYTD"
"order qty order calendar YTD"
"order qty order calendar LYTD"

etc

This is incredibly cumbersome is there an alternative approach that is going to be less cumbersome? Something that doesn't result in the creation of dozens of measures?

Thanks.

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @MartinOLoughlin-5927,

Welcome to Microsoft Q&A!

In multidimensional model, you could create time calculations. It’s dynamic because the formula will work no matter what measure is being viewed. Also, it is easy to filter just the calculations you want because it’s just like any other attribute that you would filter by. Please refer to MDX TIME CALCULATIONS BUILT TO SCALE and DateTool dimension: an alternative Time Intelligence implementation to get more details.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MartinOLoughlin-5927 avatar image
0 Votes"
MartinOLoughlin-5927 answered

Thanks for the pointer

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.