compare dmr time dimension with current date

[erwink]

Hi there

I've a crosstab with month and two values. In a third column I would like to display one or the other value depending if the current date is before or after the month of the crosstab

if ( currentMember([Dimensions].[BYTime].[BYTimeH] ) > current_date then ( A ) else ( B )

I've no clue how to transform one or the other side of the compare so the values are comparable

Thank you for your help

[Lynn]

DMR sometimes lets you do things you couldn't do with a cube. I wouldn't normally use case or if/then logic when working with dimensional sources. I'm not sure if this is the best way to go about it, but I approximated something using the GO Data Warehouse (analysis) package which is DMR. The report spec is attached.

I'm showing months as columns, filtered for 2013 and order methods as rows. Nested under the columns I've got planned revenue and revenue. I then built a data item that displays planned revenue prior to April of 2013 and revenue for that month or later. I had to pretend that April 2013 was the current month because there isn't any data after July of 2013 in the sample database. I left all three metrics in the report so you can run and see that it does the right thing, but in a real scenario you'd just have the calculated column.

The expression is as below. The first thing you need to understand is that currentMember returns a MUN which you can't use in a comparison. The business key in this source is YYYYMM, so I use the roleValue function to get that. In your source perhaps the caption will suffice. This may not work for you if you don't have a value like this for your month members.

Next I use a macro to find the current month and year, formatted the same as my month key. The sq function puts single quotes around the result. If you look at the expression in the report you'll notice an _add_years and _add_months function around the current timestamp. This is because it is January 2016 and I wanted to wind back the clock to April of 2013 to simulate the example for this data set.

Code Select Expand


if ( roleValue ( '_businessKey', currentMember ( [Sales].[Time].[Time] ) )
    <
    #sq( timestampMask ( $current_timestamp, 'yyyymm' ) )#
    )
then ( [Sales].[Sales fact].[Planned revenue] )
else ( [Sales].[Sales fact].[Revenue] )


I couldn't get the appropriate caption on my data item which makes me think there is a more elegant way to do this. Maybe someone else will chime in with a better approach.

[erwink]

Good Morning Lynn

Many thanks for your feedback. I'll test it upfront.

You say " I'm not sure if this is the best way to go about it". I've to admit that I'm also not happy with it but didn't found any solution to my issue. Also I'm surprised that I seem to be the only one needing it.
Your proposal is just an step, not the final need.

What I want to achieve is a BY Forecast. Means, running total from start BY to now with actual spent data and then up to end BY with plan data

If you have the state of the solution for such a need I'll be more than happy. Can be a solution in DM, FM or RS

Thank you
erwin

[Lynn]

Whenever there is a struggle to implement seemingly basic reporting requirements it is generally a good idea to re-think the design of the model. Yours was likely designed without any thought toward this particular reporting requirement. Many of us on Cognoise post responses indicating a need to go back to the model design and all too often the original poster replies back saying they have no ability to modify the source and must implement the functionality in the report. If you can modify your DMR then by all means go that route.

In your original post you indicated conditionally displaying one measure or the other depending on the date criteria. For that situation I'd suggest you build a third measure into the model that resolves to A or B as appropriate using the date criteria. Then the report author would just drag that measure into the report without any further monkeying around.   

You've now indicated an additional aspect to the requirement which is a running total between two time periods. You didn't elaborate on what the start and end periods are based on. Is the user going to answer prompts to determine the range or is it pre-defined based on aspects in your date dimension? I'm afraid you haven't provided quite enough information on this new requirement for me to offer much more. Maybe someone else who's done more with planning related models has a better idea of what you're after.

[erwink]

Good morning Lynn

Again many thank for your hint. I'll re-think the whole. I had always this report in mind but, well, just docu and internet to learn.

I've two sources of data. The one is Planed effort per employee per month per internal order. The other is effective spend cost per employee per month per internal order.
Report user want to see the plan, spend and forecast where it will end. This more or less aggregated on the internal order hierarchy.
I have not for every A (plan) a corresponding B (spent) value.
The running total is always for a BY. I use total([Forecast] within set periodsToDate(([Dimensions].[BYTime].[BYTimeH].[Year], currentMember ([Dimensions].[BYTime].[BYTimeH] )))
The Forecast is for now the issue

If I follow your hint, I would create a Forecast measure and copy all SPENT to it, and all future PLANNED based on the date.
Today I've two fact tables. So your suggestion would be to merge those in a new third fact table.

Attached a screen shot of the report I've build so far. I would have loved to load also my DM documentation but I don't want to push you to more support

Have a great day
erwin