This topic contains 74 replies, has 2 voices, and was last updated by 
-
Posts
-
Warren,
Congratulations on creating the cost table.
If you created a formula for the cost table like the one I used, you should not need to worry about creating measures to filter out costs because the formula for creating the cost table only creates monthly cost records if the CancelDate is blank or the month precedes the CancelDate (double-bar is an “or”):
ISBLANK ( [CancelDate] ) || [MonthStart] < [CancelDate]
Your next step may or may not be a challenge. If all costs (payments to vendors) are made once each month, you should just need to enter the cancel date in the hardware source table (and inform the vendor) and refresh the data in Power Pivot. If vendors are paid at different intervals than months, then that will probably result in some complications.
Out of curiosity, is the source for your data stored in a relational database, like SQL Server, or is it stored in another format?
No, it’s stored in my tracking sheet /spreadsheet. I’ll need to update that data source regularly to see the updated cancellations.
<span style=”line-height: 1.5;”>I was laying in bed thinking about the filtering and was thinking your month table could help me filter out future cancellations if I had a relationship with the tracking table.</span>
<span style=”line-height: 1.5;”>I may also want to create a new table that tracks forecasted server decommissioned. </span>
Hello Warren,
An option to creating another table to track forecasted decommissions would be to add a column in the original tracking table for something like “Forecast Date”; but whatever works best for your application. Also, sometimes transaction tables have a status column, that in your case could refer to the cancellation date as either actual, planned or forecasted, but the extra work here would be your measures would need to express whether you were calculating for actual, planned or forecasted.
Also, how did you create you cost table (did you create it through a linkback table formula or some other way?)
Hi Tom,
I could add some columns to the tracking sheet for the forecasted decommission date.
I created the cost table using the DAX formula you gave me, I just edited it.
So, today… if I update the tracking table additional server decommissions… I don’t see how that would flow to the costs table. Am I missing something?
Warren,
In the Demo for Cost Reductions workbook, I can change the value for CancelDate in the linked hardward table, save the workbook, and choose Refresh All on the Data tab; then the Costs table updates.
If your data is in an external workbook, you may need to choose Refresh in the Power Pivot window and then to Refresh All on the Data tab. If you use Power Query to import your data, you have some additional options: on the Data tab, in the Connections group, click the Connections button, in the Workbook Connections dialog, select a table, then click the Properties button and see what can be enabled under the Usage tab.
I think that’s what I was missing. After I created the costs table, I deleted the source table.
So, it’s not really calculated via a measure — That’s where I thought we were going. Calculate (Sum, if cancellation date is < current month #. (I think that’s right)
What you’ve done is create a process where the underlying costs table is updated when the tracking sheet is updated and all is refreshed. So, there’s no need for any filtering. Correct?
Hello Warren,
Correct.
Regarding your reference to Calculate, remember to also test for blanks in the cancellation date.
Your response to get rid of the Months table made a lot of sense to me because the same information could be gathered from the Calendar table, given it is for the same date range (see the submitted “Revised Demo” workbook).
Getting the job done with two tables is definitely better than three.
The formula for the linkback table now looks like this:
EVALUATE
SUMMARIZE (
FILTER (
CALCULATETABLE (
CROSSJOIN (
Hardware,
SUMMARIZE (
FILTER ( CALENDAR, [DayOfMonth] = 1 ),
[YearMonth],
“MonthStart”, MIN ( Calendar[Date] )
)
)
),
ISBLANK ( [CancelDate] )
|| [MonthStart] < [CancelDate]
),
[SerialNo],
[MonthlyMaint],
[MonthStart]
)
ORDER BY [SerialNo], [MonthStart]Hello Warren,
Correct.
Regarding your reference to Calculate, remember to also test for blanks in the cancellation date.
Your response to get rid of the Months table made a lot of sense to me because the same information could be gathered from the Calendar table, given it is for the same date range (see the submitted “Revised Demo” workbook).
Getting the job done with two tables is definitely better than three.
The formula for the linkback table now looks like this:
EVALUATE
SUMMARIZE (
FILTER (
CALCULATETABLE (
CROSSJOIN (
Hardware,
SUMMARIZE (
FILTER ( CALENDAR, [DayOfMonth] = 1 ),
[YearMonth],
“MonthStart”, MIN ( Calendar[Date] )
)
)
),
ISBLANK ( [CancelDate] )
|| [MonthStart] < [CancelDate]
),
[SerialNo],
[MonthlyMaint],
[MonthStart]
)
ORDER BY [SerialNo], [MonthStart]Hi Tom,
First, thanks again for all your help and responses. I think I’m making progress since I’m thinking about filter context, how calculate works, FILTER, ALL, etc.
Probably better that I stopped watching videos and reading books and rolled my sleeves up.
Since we’re doing it this way, it would seem like the cancellation logic in the table creation will have to get much more complicated. I’ve written it out in VBA and have to go back and remember what I did.
Product 1 – Maintenance cancellation is effective immediately, so that’s how you have it.
Product 2 – Maintenance can be updated 2 times a year.
If between 1/1 – 3/31 = 9 months of in year savings.
If between 4/1 – 8/31 = 3 months of in year savings
If 9/1 – 12/31 = 9 months of savings in the next calendar year.
Product 3 – Will be going away this year.. so, it’s – month = 9 – (cancelmonth + 2)
The “2” is a 60 day lead time to impact maintenance.
Or should we change approach?
Seems like this could happen in a calculate cost measure for each product.
– Warren
Warren,
Before writing out the formula in DAX, it will be important to know the logic for all of the scenarios for cancellation. Are there more than the 3 examples above?
No, just 3 products.
Warren,
Trying to better understand product 3. This is my train of thought: Product 3 is going away this year, so after the month of September ( 9 ), there will be no more monthly cost at all for this product. However, if cancellation is given before September of this year, there is a two month delay before monthly maintenance costs stop.
Ha, yes. Exactly. Not sure why, but that’s how it is.
Warren,
Rethinking approach.
Given the calculations for end of monthly maintenance cost and the complexity they would introduce, I think the best approach would be to determine the actual first month of cancellation in the source workbook using your VBA.
Power Pivot’s niche its ability to quickly aggregate data (the fewer “internal” calculations there are, the better). Preparing data for aggregation (which is the step we are on now) is the niche of Power Query. However, given the project we are working on, I think your VBA code to determine the actual first month of cancellation would be easier to implement thank working with Power Query.
Once we know the actual first month of cancellation, we should only need a small tweak in the code to generate the costs table.
Let me know what you think.
I could change my VBA code to do something like that. Right now, it calculates the yearly financial impact of those cancellations and puts them in year columns.
How about a calculated column? Seems like this would be a decent use for this issue.
The forum ‘Power Pivot’ is closed to new topics and replies.