Good Afternoon,
We currently have a few apps where we extrapolate the 2021 yearly sales using the YTD 2021 run rate in the formula below. These apps are loaded with new data on a daily basis.
(Sum({<SaleYear={'$(vYEAR)'}>}[Sales]))/
NetWorkDays(YearStart(vSaleMaxDate),Today()-1)
*NetWorkDays(YearStart(vSaleMaxDate),YearEnd(vSaleMaxDate))
with vYEAR = 2021
and in the load script
Temp:
Load
min (Saledate) AS minDate,
max (Saledate) AS maxDate
Resident Salesdata;
Let vSaleMinDate = Num(Peek('minDate', 0, 'Temp'));
Let vSaleMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(vSaleMinDate) + Iterno() -1 As Num,
Date($(vSaleMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(vSaleMinDate) + IterNo() -1 <= $(vSaleMaxDate);
SaleDateCalendar:
Load
TempDate AS Saledate,
Year(TempDate) As SaleYear,
YeartoDate(TempDate)*-1 as SaleCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as SaleLastYTDFlag,
ApplyMap('QuartersMap', month(TempDate), Null()) & '-' & Year(TempDate) as SaleQuarterYear,
Month(TempDate) & '-' & Year(TempDate) AS SaleMonthYear,
If(TempDate > Date(MonthStart('$(vSaleMaxDate)', -12)) and TempDate <= Date('$(vSaleMaxDate)'-1), 'Last 12 Months','null') as Last12Months
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
I am currently developing a new app and trying to incorporate this formula, but the difference is we only receive and load the data once on a monthly basis. So using the above formula, the extrapolation will only be correct for one day out of the month.
Does anyone have any suggestions as to how to fix this? There may be a simpler formula or calculation that I am overlooking.
Thanks in advance.
