Two different YearToDate calculations on the same data

[tiawy]

Hi

I have a question about the TOTALYTD-function, that i hope somone can help me with.

I want to have two different TOTALYTD-calculations but on the same data.

The idea is a company that pays different loans each month. Some are paid in advance while others are paid in arrears.

My data looks like the following:
Period is simply the period the payment of the loan is registered. For instance 1501 is january 2015.

FactLoansPayment


[TABLE="width: 500"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Loan[/TD]
[TD]Amount[/TD]
[TD]Type[/TD]
[TD] DateKey[/TD]
[/TR]
[TR]
[TD]1501[/TD]
[TD]L1[/TD]
[TD]20[/TD]
[TD]Arrears[/TD]
[TD]15011[/TD]
[/TR]
[TR]
[TD]1501[/TD]
[TD]L2[/TD]
[TD]10[/TD]
[TD]Arrears[/TD]
[TD]15011[/TD]
[/TR]
[TR]
[TD]1502[/TD]
[TD]L1[/TD]
[TD]20[/TD]
[TD]Arrears[/TD]
[TD]15021[/TD]
[/TR]
[TR]
[TD]1502[/TD]
[TD]L3[/TD]
[TD]8[/TD]
[TD]Advance[/TD]
[TD]15022[/TD]
[/TR]
</tbody>[/TABLE]



DimDate

DateKey Type PeriodDate PaymentDate
15011 Arrears 2015-01-01 2015-01-01
15012 Advance 2015-01-01 2015-02-01
15021 Arrears 2015-02-01 2015-02-01
15022 Advance 2015-02-01 2015-03-01



The thing is, some loans are registrered in a certain period, but not necessarily paid in the same period. For instance loan L3 is registrered in period 1502 (february 2015) but is payed in advance and thus the payment date is 2014-03-01.


If I for instance chose to look at february, i would like the YearToDate (YTD) calculation for Period to show L1=40, L2=10 and L3=8, however the YearToDate calculation for Payment should show L1=40 L2=10 L3=(blank)
Since I chose february, it should only sum up amounts that has a Perioddate smaller or equal to february in YTDPeriod, and only show sum of amounts that has a Paymentdate smaller or equal to february in YTD-Payment - and since L3 has an amount with Period that is february the YTDPeriod should show L3=8, but because its Payment is in march it should show blank for YTD-Payment L3=(blank).


The desired result in a PivotTable (with a slicer on february)



[TABLE="width: 500"]
<tbody>[TR]
[TD]Loan[/TD]
[TD]Sum of Amount[/TD]
[TD]YearToDatePeriod[/TD]
[TD]YearToDatePayment[/TD]
[/TR]
[TR]
[TD]L1[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD](blank)[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD](blank)[/TD]
[/TR]
</tbody>[/TABLE]



I can get the two results with two different slicers - one on PeriodDate and one on PaymentDate, but not on the same time. If i for instance set the slicer on PeriodDate to 2014-02-01 i get this (which is correct for YearToDatePeriod but incorrect for YearToDatePayment)




[TABLE="width: 500"]
<tbody>[TR]
[TD]Loan[/TD]
[TD]Sum of Amount[/TD]
[TD]YearToDatePeriod[/TD]
[TD]YearToDatePayment[/TD]
[/TR]
[TR]
[TD]L1[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD](blank)[/TD]
[TD]10[/TD]
[TD](blank)[/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]





My measures are as follows:
SumOfAmount = SUM(FactLoansPayment(Amount))

YearToDatePeriod = TOTALYTD([SumOfAmount];DimDate[PeriodDate])

YearToDatePayment = TOTALYTD([SumOfAmount];DimDate[PaymentDate])

Would i need to make another DateDimension or two datedimensions or can this be achieved with the current data? And if yes - how would you do this?

Thank you very much!

 

[scottsen]

So, I think the main thing going astray here is that you are using power pivot's time intelligence functions (TOTALYTD), but those require a "real" date table. That would be a Calendar table that includes exactly 1 row for exactly every date in the calendar (for the range you care about). You have a situation where you might have blanks (paymentdate), and ... you have 2 columns of dates.

 

[tiawy]

Hello scottsen
thank you very much for your reply. I haven't completely understood the time-intelligence functions yet, but I guess the best bet is to make a datetable with 1 date-column then. Do i need one row for every day in a year in the date table even though I only have fact-table data for 2 dates per month?

 

[scottsen]

Yes. That's like "the definition" of a date table, it can't contain gaps in the dates.