rubrix | Rubrix , open-source framework | Dataset library

Hello MDX and SSAS people,

I was wondering how to create a time calculation measure that summarizes the selected period/month's total value for the entire year in this case Budget. So if I select 202004 from my time hierarchy it should be the total budget of year 2020 and if I select 201904 it should be the total budget of 2019.

In the picture below I have created [YTD Budget] with the following mdx code:

This might be a longshot, but maybe there is an ssas/mdx guru here that has stumpled upon the same problem regarding aggregated calculations and leap years. So what I am trying to do, is to create a PY YTD calculated measure in an ssas cube that can handle leap years. The issue I am facing is that the calculated measure becomes extremely slow based on the logic below (see code sample). Has anyone found a better way to handle leap years or has a document with best practise to share ? I am assume that if-statements and NonEmpty function could be performance deadly for a calculated measure. All tips are greatly appreciated (Doesn't have to be a solution).

I have a dimension that consists of members with the following format 100 - Sales, 200 - Purchase etc. What I wish to do, is to substring starting from the left and remove everyting after the first space. So the result would be "100" and "200" for the two examples. I have done this in sql before with the following code syntax: LEFT(MyField, CHARINDEX('', MyField) - 1)

But it doesnt seem to work when I apply the same logic in SSAS. LEFT([MyField].[MyField].Members,CHARINDEX('',[MyField].[MyField].Members)-1).

Does anyone know the syntax to accomplish the same thing in SSAS?

Best regards, Rubrix

I have a report with three different pages displayed as tabs whereas one of the pages is designed for PDF print. The issue I am facing is when I choose a specific tab to export to PDF, the two other pages/tabs are exported aswell. I only want the tab that is selected to be exported, not all of them.

The only option I see, is the "Run as PDF", there is no specific button that exports the current tab that is displayed when consuming the report.

I am using version 11.1.4 and this is when creating a report (not dashboard)

All tips, solutions and ideas are very appreciated.

Best regards, Rubrix

I have a column in my fact table that defines whether a Supplier is old or new based on the following case-statement:

I have a distinct measure "No of Customer Id" that counts the amount of unique Customer Id's that occurs for each month. What I wish to do, is to create a Calculated member that distinct Count Customer Id over a 12 month period i.e Rolling 12. If you look at "Nr of Customer Id" in 201911 you have 378 unique Id's that month but between 20181201 and 20191130 there are 1020 unique Customer Id's (R12 Nr of Customer Id).

Is this possible to do and if so how ?

I tried with (But didn't work): sum(parallelperiod([D_Time].[Year-Month-Day].[Year],1,[D_Time].[Year-Month-Day].currentmember).lead(1) : [D_Time].[Year-Month-Day].currentmember , [Measures].[Nr of Customer Id])

Best regards, Rubrix

I wish to great a calculated measure "Dispatch days" in SSAS Visual Studios that counts the number of days where there has been a sales transaction made, i.e Invoice Amount greater than 0. So if I look at 2018 it should give me the number of days that year, where there were sales. I tried using this

I need to convert a Unit measure value in a database view when two criterias doesn't match with information from a different table. So what I do have is one database view called "v_Purchase order" table and one table that is called "Alternate unit" table.

As you can see in the example I have 3 rows in the Purchase order table, where Purchase order U/M and Basic U/M match on 2 rows, but not on the third row. So what I wish is that: case when (Purchase order U/M <> Basic U/M)

then (use Alternate unit table to replace Purchase order U/M column row from Purchase order table with the Alternate U/M from the Alternate unit table and apply the Conversion factor to multiply it with Quantity to convert pallet to pieces)
else(No conversion needed)
end

The two tables are joined on Item number, Company, Alternate U/M

Which should give me this final output result:

I am following a Python for Finance tutorial where you are combining all the S&P500 stocks into one data frame, i. e. outer joining all the stored CSV files for all the different stocks.

current output:

The code for this looks like this: