Use 'Last Known' Effective Fact

[m1nkeh]

Hi,

I've got a fact table with 'effective dates' and this maps to a common dimension of what that record was effective... which is what i think is very similar to a foreign exchange fact table. the comparison becomes stronger in fact when you learn that a value in this table needs to be used to change a value in another table, just like FX would... here is my model at present:

There are no calculations involved in order to the measure 'Correct' working, other than the following DAX statement:

Correct :=SUMX (
Split,
CALCULATE (
SUM ( Sales[Value] ) * VALUES ( Split[Multipler] )
)
)

That's it, it works.. lovely... we do the calculation at the leaf level and everything rolls up nicely.

The issue i'm having is when the 'Split' table (this could be considered an FX rate table) does not have an explicit value for a Date and SaleAgent combination.. so i was messing about with attempting to 'fill in' with the last known data, while still retaining the relationship to date.... here is the data in the split table:

obviously there is a gap here with no data for the 3rd of Jan... if there was a sale on the 3rd of Jan though, we need to use the LastKnown data for that SaleAgent. In this case it comes from the 2nd Jan. Anyone have any ideas on how i can achieve this?

My thoughts are:

1) 'Fill in' the gaps in a view / ETL with everything before the model is processes
2) Disable the relationship of the 'Split' table from Date and SaleAgent and then fill in the whole with dax and then utilise the USERELATIONSHIP function in the calcualtion

I have uploaded a sample workbook to OneDrive: http://1drv.ms/1wmlExp

 

[scottsen]

The way your data "looks" you might be better served having a "default" of "1" on multiplier, doing a lookup on date... and if BLANK() go with the "1".

But uh... that assumes some other biz requirements, so maybe ignore that

Something like this should give the "most recent multiplier before and including today":

=CALCULATE(
           LASTNONBLANK(Split[Multipler], SUM(Split[Multipler])), 
           FILTER(ALL(Dates[Date]), Dates[Date] <= MAX(Dates[Date]))
)

 

[m1nkeh]

hi, yeah most of the time the default split will be 1, so £100 is split just between one agent, therefore that is multiplied by 1, good shout i might just do that

However... i can't actually get this to work, so i think i'm just going to do a cross join in a view within SQL Server to load the data into the model. i need to get this working ASAP, but it could be improved at a later date i hope

I've messed around with it for ages now and two things are foiling me:

1) I can't seem to implement last non-empty while 'considering' a dimension correctly... although i think it's close
2) When i do the multiplication by the calcualted most recent multipler... it can't be done because there is a defined relationship between the tables and that record does not exist (3rd Jan)

 

[scottsen]

Hmm... I can't remember if I tried my measure with a slicer. You tried that and it didn't work?

 

[Tianbas]

if I understand right your split-table has no row for for Jan 3rd but your sales table has it ? I would try to bring the Multiplier as a calc column in the sales table. The gaps can be filled with an EARLIER formula. Thats what we do with our exchange rate tables that have some gaps on weekends and holidays.