measure doesn't work when using related table slicer

[ctappan]

Hey all, looking for some assistance. I've got a Loans and PAR (All) table with unique records for each Loan ID, and this is used in lots of other visuals to filter by customer number, loan product type, etc. Then I've got a Loans and PAR (Trend) table that has a record for each day with balances and such, and there's a one to many relationship between the Loans and PAR (All) and Loans and PAR (Trend) table based on the Loan ID.

With some help from this board and other forums, I've put together a measure that returns the balance of the same loan from a previous period--day, week, month, quarter, year--using a comparison unit slicer. This measure works consistently and returns expected values at the totals level and at the loan level if I slice the Loans and PAR (Trend) table based on Loan ID.

But if I add a slicer from the Loans and PAR (All) unique-record table or other tables with relationships to the Loans and PAR (All) table, such as customer, product type, etc, the measure returns blanks rather than the expected value.

I'm including the measure formula below. I know it's a lot to look at, so thanks in advance to anyone who thinks they know why it's not working.

Previous Principal Outstanding Balance = 
    IF(HASONEVALUE('Comparison Period'[Unit]), //if no selection or multiple selections, returns "please select period." (at bottom)
        SWITCH( //like a nested IF statement; if "Day",x,if "week",y,if(...
            FIRSTNONBLANK('Comparison Period'[Unit],'Comparison Period'[Unit]),//I don't understand this
                "Day",
                IF(and(HASONEVALUE('Report Date (Daily)'[RowNum]),min('Report Date (Daily)'[RowNum]) > 1), //without this, it breaks because of first value in any record set
                    var prevWorkDay = LOOKUPVALUE('Report Date (Daily)'[Report Date],'Report Date (Daily)'[RowNum],min('Report Date (Daily)'[RowNum])-1) //get the date of the row one row less than this row
					return
                    CALCULATE(SUM('Loans and PAR (Trend)'[Principal Outstanding Balance]),'Report Date (Daily)'[Report Date] = prevWorkDay), //sum get the value where the report date is equal to the variable prevWorkDay
                    blank()
                ),
                "Week",    
                IF(AND(HASONEVALUE('Report Date (Weekly)'[Week Number]),min('Report Date (Weekly)'[RowNum]) > 1),
                    var prevWeekEnd = LOOKUPVALUE('Report Date (Weekly)'[Last Working Day of Week],'Report Date (Weekly)'[RowNum],min('Report Date (Weekly)'[RowNum])-1)
					return
                    CALCULATE(SUM('Loans and PAR (Trend)'[Principal Outstanding Balance]),'Report Date (Daily)'[Report Date] = prevWeekEnd),
                    blank()),
                "Month",
                IF(and(HASONEVALUE('Report Date (Monthly)'[RowNum]),MIN('Report Date (Monthly)'[RowNum]) > 1),
                    var prevMonthEnd = LOOKUPVALUE('Report Date (Monthly)'[Last Working Day of Month],'Report Date (Monthly)'[RowNum],min('Report Date (Monthly)'[RowNum])-1)
					return
                    CALCULATE(SUM('Loans and PAR (Trend)'[Principal Outstanding Balance]),'Report Date (Daily)'[Report Date] = prevMonthEnd),
                    blank()),
                "Quarter",
                IF(AND(HASONEVALUE('Report Date (Monthly)'[RowNum]),MIN('Report Date (Monthly)'[RowNum]) > 3),
                    var prevQtrEnd = LOOKUPVALUE('Report Date (Monthly)'[Last Working Day of Month],'Report Date (Monthly)'[RowNum],min('Report Date (Monthly)'[RowNum])-3)
					return
                    CALCULATE(SUM('Loans and PAR (Trend)'[Principal Outstanding Balance]),'Report Date (Daily)'[Report Date] = prevQtrEnd),
                    blank()),
                "Year",
                IF(and(HASONEVALUE('Report Date (Monthly)'[RowNum]),min('Report Date (Monthly)'[RowNum]) > 11),
                    var prevYearEnd = LOOKUPVALUE('Report Date (Monthly)'[Last Working Day of Month],'Report Date (Monthly)'[RowNum],min('Report Date (Monthly)'[RowNum])-12)
					return
                    CALCULATE(SUM('Loans and PAR (Trend)'[Principal Outstanding Balance]),'Report Date (Daily)'[Report Date] = prevYearEnd),
                    blank())
                    ),
                "Please select period.")

[v-yulgu-msft]

Hi @ctappan,

Please post some dummy data to better illustrate your scenario.

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[ctappan]

Thanks Yuliana.

Here's some basic tables and basic data.

Loans and PAR (All)
Loan ID	Product
1	Long-Term
2	Long-Term
3	Short-Term
4	Short-Term
5	Short-Term
6	Long-Term
7	Long-Term
8	Short-Term
9	Short-Term
10	Short-Term

Loans and PAR (Trend)
Loan ID	Balance	Previous Balance (measure we're trying to calculate; here assuming the "daily" Unit has been selected)	Report Date
1	300		6/1/2018
1	250	300	6/2/2018
1	200	250	6/3/2018
1	500	200	6/4/2018
2	400		6/3/2018
2	300	400	6/4/2018
2	200	300	6/5/2018
3	1000		6/2/2018
3	950	1000	6/3/2018
3	900	950	6/4/2018
3	850	900	6/5/2018
3	800	850	6/6/2018
3	750	800	6/7/2018
4	600		6/4/2018
4	500	600	6/5/2018

Report Date (Daily)
Report Date	RowNum
6/1/2018	1
6/2/2018	2
6/3/2018	3
6/4/2018	4
6/5/2018	5
6/6/2018	6
6/7/2018	7
6/8/2018	8
6/9/2018	9
6/10/2018	10

Comparison Unit

Unit

Daily

Weekly

Monthly

Quarterly

Yearly