Hi All
I know why my calculation is not giving the result I expect but I want to explore alternatives with my expression before I start trying to "create" data in the model.
I have a main fact table at this client with a master calendar, various master data tables and then a new adjusted table with the correct number of licensed machines at specific dates. I'll attach a snapshot image.
This additional table is brought into the model with a LM_KeyV2. This key is: AutoNumberHash128(Date(License_Day_Indicator,'YYYY/MM/DD')) & '|' & MDSiteKey. ( Date & Sitekey)
My overall KPI for machines is take the most recent value for all sites and sum them together using Sum and AGGR. I am happy with the result on an overall KPI basis.
sum(
{< SiteStatus = {'Live','Temporary Closure'} , ActualDate = {"<=$(vMaxDate)"} >
-
< SiteType = {'Warehouse','GPI Warehouse'} >}
AGGR ( FirstSortedValue( {< License_Day_Indicator = {"<=$(vMaxDate)"}>} distinct licenced_machines , - License_Day_Indicator ) , MDSiteKey ))
HOWEVER, I now have to use this calculation on a pivot with month and year from the master calendar and I have identified something with the client's data where certain sites that are "Temporary Closure" actually stop having records in the table every day like the live sites. The problem is that on a date axis, it's not including the temporary closure sites after certain dates, instead of still counting the number of machines. That's the only thing that's giving me a difference.
My solution was to look at writing data into the underlying data (I'll probably need to do a separate post for that) or trying to develop a formula for the date axis that will show the most recent number of machines per site, across the years and months. So if I'm looking in a Pivot record for April 2019 and the site became "Temporary Closure" in January, then the January figure is still the most recent and the correct figure to use.
James
