[dspbluesParticipant Started: 1Replies: 41Total: 42]

Thanks again for all of your help through out all of this. I’ve learned a lot.

I’ll be a Senior Global Program Manager for IBM’s Transition and Transformation Team. I hope I’ll need to use PowerPivot at some point. Also, VBA and my other Excel skills.

I’ll continue to be a member of the community and hopefully help others.

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Hi Tom,

Looks like I’m moving to a new job. So, I won’t be needing to use this. The good news is I read both of Rob’s books, Kasper’s, and watched all of Rob’s 22 hr course. 🙂

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Hi Tom,

It’s really all over the place. The SN on the system is one source. But the vendors may not use that completely and remove the first 2 digits and replace them with 00. The internal sources are manually entered and may or may not use the full serial #.

There are probably 3-4 sources of 3000 serial #s I’m tracking. There are some duplicates, but I can try to work around that or build a solution for that. I thought it might be useful to create some kind of table that builds a relationship between these different “unique” serial #s. 🙂

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Hi Tom,

Question for you. I have a situation where the Serial # we are tracking should be unique and should match between all sources of record, but they often don’t.
I assume this is a common issue. What’s the best way to handle it? A lookup table that lists the various forms of the serial # across the various sources?

[dspbluesParticipant Started: 1Replies: 41Total: 42]

I think I did it…. thoughts?

 

Cost Post Cancellation:=CALCULATE([Sum of Maintenance Cost],

filter(Costs, Costs[MonthStart]>=RELATED(Tracking[Maintenance Start Date])
&&

Costs[MonthStart]<related(Tracking[Cancellation Effective Date])

||

ISBLANK(related(Tracking[Cancellation Effective Date]))

&&

Costs[MonthStart]>=RELATED(Tracking[Maintenance Start Date])))

[dspbluesParticipant Started: 1Replies: 41Total: 42]

or… maybe this isn’t done in that table, my bad. My calculate cost post calculation measure does this now. I tried this, but it’s not working. Thoughts?

 

Cost Post Cancellation:=CALCULATE([Sum of Maintenance Cost], filter(Costs, ISBLANK(related(Tracking[Cancellation Effective Date]))||Costs[MonthStart]>=RELATED(Tracking[Maintenance Start Date]) && Costs[MonthStart]<related(Tracking[Cancellation Effective Date])))

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Hi Tom,

I’m trying to update the linkback table to introduce the concept of maintenance start dates. I added the following line that’s bolded. Any thoughts on how I’d achieve this?

 

EVALUATE
SUMMARIZE(FILTER(CALCULATETABLE (CROSSJOIN (Tracking,Months)),
ISBLANK([Maintenance Cancellation Request Date])
||[Maintenance Cancellation Request Date]>date(2014,12,31)
||[Maintenance Start Date]>=[MonthStart]
),
[Frame SN],
[Monthly Maintenance],
[MonthStart],
[Cost Type]
)
ORDER BY[Frame SN],[MonthStart],[Cost Type]

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Tom,

Thank you so much for all your help.

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Tom,

Is there a way to filter out quarterly & yearly sums from a pivot?

I have a situation where my monthly values are correct, but not when rolled up.

[dspbluesParticipant Started: 1Replies: 41Total: 42]

I ended up filtering them out via the linkback table. I think I did this correctly:

EVALUATE
SUMMARIZE(FILTER(CALCULATETABLE (CROSSJOIN (Tracking,Months)),
ISBLANK([Maintenance Cancellation Request Date])
||[Maintenance Cancellation Request Date]>date(2014,12,31)
),
[Frame SN],
[Monthly Maintenance],
[MonthStart]
)
ORDER BY[Frame SN],[MonthStart]

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Sorry for all the posts.

I was able to get around that by basically copying the values in a new column. Not the best way, but it got me back in action.  Here’s the formula I have that works..

 

Cost Post Cancellation:=CALCULATE([Sum of Maintenance Cost], filter(Costs, ISBLANK(related(Tracking[Cancellation Effective Date]))||Costs[MonthStart]<related(Tracking[Cancellation Effective Date])))

One Issue I have is that my tracking table has 2014 cancellations in it, but my cost table starts at 1/1/15.  Should I filter those 2014 cancellations out of my cost table? (probably)… .or filter them out in the measure?

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Tom,
Any ideas on this one?

 

============================
Error Message:
============================

The following system error occurred: Type mismatch.
Datatype conversion failed for [Table: ‘Tracking’, Column: ‘Maintenance Cancellation Effective Date’, Value: ”].

—————————-
The following system error occurred: Type mismatch.
Datatype conversion failed for [Table: ‘Tracking’, Column: ‘Maintenance Cancellation Effective Date’, Value: ”].
============================
Call Stack:
============================

at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteAMOCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitUpdates()
at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
—————————-
at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
at Microsoft.AnalysisServices.Common.SandboxEditor.DataTypeSelectionChanged(String newSelectedString, TableWidgetGridControl grid, List`1 selectedColumns)

============================

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Hi Tom,

The “effective date” takes into consideration all that logic we talked about for the different products. A very large IF/then statement to determine when cost will actually stop.

Future = Forecasted decommission. I should probably have a “Forecasted Migration Effective Date” Also… since you can’t claim that cost savings/avoidance right away.

For example, 1 product only allows maintenance updates twice a year. Another allows for it next day.

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Here’s how I translated that…

 

Cost Post Cancellation:=CALCULATE([Sum of Maintenance Cost], filter(Costs, ISBLANK(Tracking[Maintenance Cancellation Effective Date])||Costs[MonthStart]<Tracking[Maintenance Cancellation Effective Date]))

Now, how do I add the related?

 

[dspbluesParticipant Started: 1Replies: 41Total: 42]

Thanks. So right now, my cost table is very simple. [Frame SN], [Monthly Maintenance], [Month Start], [Cost Month], [Cost Year].

Nothing has been filtered at all.

In the tracking table, that’s joined via Frame SN, I have:

[Maintenance Cancellation Effective Date], [Future Maintenance Cancellation] (date)

I haven’t added the “F” (Future) or “A” (Actual) column yet, but I could.

 

Do I need to change what data is included in the costs table? I thought Tracking filtering could flow into the costs table.

Attachments:

You must be logged in to view attached files.

[Author]

Posts