I have data as below to calculate the Total Amount.
In this FY is from July 1st - June 30th.
Below should be the calculation.
FY for 2019 is July 2018 - June 2019.Based on the start date and End date there are 4 Months in 1st row and 8 months in 2nd row that falls under the 2019 FY.so Expense would be (10,855 * 4) + (11,107*8) = 132,283.
FY for 2020 is July 2019 - June 2020.Based on the start date and End date there are 4 Months in 2nd row and 8 months in 3rd row that falls under the 2020 FY.so Expense would be (11,107*4) + (11,410*8) = 135,717.
I see. There is a misstake done in the join. The join should be on the calendar not the transaction. When joining an non distinct table in to transactions your tranactions will duplicate.
You will get an synthetic key, but dont worry about it. It is ok to leave an syntethic key when using intervalmatch. If you do not want it then you can remove it after the join by creating a concatenated field of expence start and end.
LET _mindate = '7/01/2016';
LET _maxdate = '6/30/2020';
YearName(Month,0,7) as Year,
MonthName('$(_mindate)',iterno()-1) as Month
MonthName('$(_mindate)',iterno()-1) < '$(_maxdate)'
RowNo() as TransID,
date(expense_start) AS expense_start,
date(expense_end) AS expense_end
(ooxml, embedded labels, table is expense_schedule)
date(expense_start,'M/DD/YYYY') >='7/01/2016' and
/*IntervalMatch your Start and End against the Month-value (All three are duals with a numeric value)*/
Left join (Calendar)
Plees ekskuse my Swenglish and or Norweglish spelling misstakes