Calculated Column vs Calculated Table vs <other>


#1

Looking for some advice on the best way to tackle a scenario I need to display. I have a table with Sales with a date opened and date closed. What I would like to do is take the Average Days to Close for closed records and add that time to the Date Opened for Opened records to give a new dimension of Estimated Close. I then need to display my total sales by Est Date Closed.

I have two ways of solving this so far, add a calculated table limited to only open records of the type i define and add the column based on Date Opened + [Avg Days to Close] , then link to my dates table, or add a calculated column to my sales table. Pros/cons? Anyone have another idea on how to tackle? The need to display total over time I think restricts me to one of the above, but its been a long day

Thanks
-Randy


#2

Any reason you can’t do this in measures only? I think actually you probably do need some column due to the fact you want to show the information by estimate close date (which will be calculated)

You can utilize iterating functions to work through the first bit.

First create the Average Days to Close using AVERAGEX. Within the AVERAGEX you can isolate which ones have closed by using an IF statement.

I would honestly break some rules here and get a calculated column into your sales table. This simplifies it.

The additional calculated table to me seems a little redundant and probably not required to get this working.


#4

I think I am still missing something here. When I create my lead date column, and link it to my Dates table, the relationship does not seem to hold. I am creating a measure to get the average age for the order type I want for the past 90 days. I need this to be based on the past 90 days regardless of other context for this exercise

When I add my column, I am adding a lead date only to those records with the correct order type and status. I see the column populate correctly and create a relationship. When I try to use that relationship, it appears to have no matches.

Attached a test workbook with some stripped data

EDNAhelp.pbix (921.3 KB)
EDNAhelp.xlsx (1.6 MB)

EDIT - And I think its to do with format. When I change the column to have an output like

DATEVALUE( Test[Date Received] + [Avg Closed Equipment SO Age] ), BLANK())

All seems to work


#5

Yep nice one, I think that was probably all you were missing there.

Think you’ve got it sorted now?


#6

Yeah, I believe so, we’re seeing exactly what we were expecting to. Thanks!