Calculate Last Date and Amount Where Full Write-Offs have been excluded

[HeatherD]

Greetings, all.

I'm a long-time Power Query user, but am very new to Power Pivot and DAX.  I am working in Excel and I have created a number of measures, used slicers to filter, and have even dabbled with some CUBE functions with success.  However, there is one DAX measure that I just cannot figure out and I am hoping someone here has some DAX magic they can share.  Thank you in advance for your help!  

A little background before the measures I'm stuck on

Full write-off - when CampaignID and Pledge (row) = CampaignID and Write-Offs (row)

So this IS a full write-off

Company	Date	CampaignID	Pledge	Write-Offs
Company A	7/29/2019	A19	$5,000
Company A	10/30/2020	A19		$5,000

This IS NOT a full write-off

Company	Date	Campaign	Pledge	Write-Offs
Company A	7/29/2019	A19	$5,000
Company A	10/30/2020	A19		$2,500
Company A	10/31/2020	A19		$2,500

I need to create 2 measures, LastPledgeDate and LastPledgeAmount, for each Company where I filter out dates/amts that are a full write-off.  If all pledges have been fully written-off, then return no value (blank).

I have a dim Calendar table where Calendar[Date] is related to Transactions[Date]

Transactions (fact table)

Company	Date	CampaignID	Pledge	Write-Offs
Company A	7/29/2019	A19	5000	0
Company A	7/8/2020	B20	1000	0
Company A	10/30/2020	A19	0	5000
Company A	10/25/2021	B20	0	742
Company A	12/15/2021	C21	5000	0
Company B	7/29/2019	A19	5000	0
Company B	7/8/2020	B20	35000	0
Company B	10/30/2020	A19	0	5000
Company B	10/27/2021	B20	0	35000
Company B	12/15/2021	C21	15000	0
Company X	7/30/2019	A19	5000	0
Company X	7/8/2020	B20	40000	0
Company X	10/26/2020	A19	10905	0
Company X	12/11/2020	A19	0	10905
Company X	10/25/2021	B20	0	20000
Company X	10/31/2021	B20	0	20000
Company X	12/15/2021	C21	30000	0
Company Z	7/29/2019	A19	75000	0
Company Z	7/8/2020	B20	35000	0
Company Z	10/30/2020	A19	0	35000
Company Z	10/25/2021	B20	0	20000
Company Z	10/30/2021	B20	0	15000
Company Z	12/15/2021	C21	60000	0

Here are the results I would see in the PivotTable.  If the today was:

10/1/2020, then…
	LastPledgeDate	LastPledgeAmt
Company A	7/8/2020	$1,000
Company B	7/8/2020	$35,000
Company X	7/8/2020	$40,000
Company Z	7/8/2020	$35,000
11/1/2020, then same as previous except….
Company X	10/26/2020	$10,905
12/31/2020, then same as previous except….
Company X	7/8/2020	$40,000
12/1/2021, then same as previous except….
Company B	(blank)	(blank)
12/31/2021, then ….
Company A	12/15/2021	$5,000
Company B	12/15/2021	$15,000
Company X	12/15/2021	$30,000
Company Z	12/15/2021	$60,000

[v-kkf-msft · Accepted solution]

Hi @HeatherD ,

Please try the following measures:

IS full write-off = 
COUNTROWS (
    FILTER (
        ALLSELECTED ( Transactions ),
        Transactions[Company] = MAX ( Transactions[Company] )
            && Transactions[CampaignID] = MAX ( Transactions[CampaignID] )
            && Transactions[Write-Offs] = MAX ( Transactions[Pledge] )
            && Transactions[Date] <> MAX ( Transactions[Date] )
    )
)

LastPledgeDate = 
IF (
    SUM ( Transactions[Pledge] ) > 0,
    CALCULATE (
        MAX ( Transactions[Date] ),
        FILTER ( ALLSELECTED ( Transactions[Date] ), [IS full write-off] = BLANK () )
    )
)

LastPledgeAmt = 
LASTNONBLANKVALUE (
    FILTER ( VALUES ( Transactions[Date] ), Transactions[Date] = [LastPledgeDate] ),
    SUM ( Transactions[Pledge] )
)

    

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HeatherD]

Hi @v-kkf-msft.  Thank you for your reply! 

I can see the calculations working in the pbix file you provided and attempted to recreate it in Excel using Power Pivot.  The LastPledgeDate works great so far! 

I get an error on the LastPledgeAmt:  "Failed to resolve name 'LASTNONBLANKVALUE'. It is not a valid table, variable, or function name."

Since it works in PowerBI and not Power Pivot/Excel, it seems to be something specific to the latter so I'll do a little research (unless you happen to know off-hand).  The good news is that of the two measures, LastPledgeDate is the most important.

[Whitewater100]

Hi Heather:

Please see solution file. I hope this is what you are looking for!

https://drive.google.com/file/d/1Ux2apB_c3wRcixCGiooSz_fTaFbU6jpp/view?usp=sharing 

[HeatherD]

Hi Whitewater:

Thank you for your quick response!  I looked at what you provided over the weekend but wasn't seeing the values I was expecting.  It looks like the date shows the last transaction date, write-offs included, as opposed to the last pledge transaction date.  I was hoping to figure that out and then figure out the last pledge amount, but no luck so far.  I'm also looking at another suggestion that was posted.