SSAS Multidimensional model presented in Power BI

Question

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.

Answer 1

Hi @Anonymous ,

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.

Answer 2

Thanks for the pointer