set variable to slicer selection

[jdormer]

I want to build a report that displays the total Invoiced Amount, and the revenue earned since the last invoice. To accomplish this, I am trying to create a column that stores the Last Invoice Date, and then create a second column that captures the revenue since the last invoice date. I tried to create the LastInvoiceDate column using:

LastInvoiceDate = FORMAT(LASTDATE(Invoices[Created]), "Short Date")

This appears to return all invoice dates, not just the last. I also tried:

LastInvoiceDate = FORMAT(MAX(Invoices[Created]), "Short Date")

This returns a single date for my entire dataset, but I need to capture this value for each client. Can I use a slicer to add a variable to this formula, to get the MAX date for the client selected in a slicer? 

I am new to using PowerBI - any help that can be provided is much appreciated. 

Thanks,

Joe

[MattAllington]

I would like to help. When you say "revenue earned since last invoice", what does that mean?  My assumption is that revenue since the last invoice must be the value of the current invoice assuming you are talking about incoming monies.  There is no revenue until you create an invoice, technically until they pay.  Maybe you thinking something different. Maybe you mean "how much billable income do I have since the last invoice".  It is not clear. 

I am am pretty sure at you don't want to be creating a calc column

[jdormer]

Hi Matt, 

Thanks for the reply. Looking at my original post, I see it's not very clear, and I think I was probably overthinking this with the variable/slicer comment. Let me try to clarify. 

I have a 'Time' table that contains time entry information including a 'Revenue' (refers to "billable income," as you put it) column for each entry/row. 

I have a separate 'Invoices' table that contains information regarding invoices created including 'Amount' and  'CreatedDate.'

I have another 'Engagement' table that includes 'PO Amount.'

All of these tables reference an 'Engagement ID' column. 

My goal is to create a report that displays the original PO amount for the engagement, the billable income that has been invoiced, the billable income that has been earned (work performed) since the last invoice, and the amount remaining on the PO. 

I hope this helps provide some clarification. Let me know if you have additional questions. 

Thanks, 

Joe

[Anonymous]

Hi @jdormer,

   do you need something like this?

The "LastInvoiceDate" is a Measure.

Let me know if it works (and if it's what you are searching for)

#I'M Not An Expert#

[jdormer]

Thanks for the feedback all. I picked this back up after tackling some more pressing projects over the last few weeks. With the comments here, I'm on the right track. 

[jdormer]

And I'm back... I have to be missing something simple. 

I have:

Measure - LastInvoiceDate = FORMAT(MAX(Invoices[Created]), "Short Date")

Measure - RevSinceInvoice = CALCULATE(
SUM('Time'[Revenue]),
FILTER('Time', FORMAT('Time'[Date],"Short Date")>'Invoices'[LastInvoiceDate]
))

Existing columns 'Invoices'[Amount] and 'Engagement'[POAmount].

I'm trying to create a calculated column that gives the amount remaining on the PO. I tried:

RemainingOnPO = Engagement[POAmount]-CALCULATE(SUM('Invoices'[Amount]))-([RevSinceInvoice])

However, the output is equal to POAmount - Amount, and does not subtract the RevSinceInvoice value. I'm assuming this has to do with RevSinceInvoice being a measure? How can I subtract RevSinceInvoice from the POAmount?

[TheOckieMofo]

You probably need one or two calculated columns utilizing the Earlier Function (or the new variable method described by the Italians). Look on PowerPivotPro.com for some tips on using this formula.  Essentially, the function allows you to pass row level filters through a CALCULATE function. That should allow you to create what you're looking for.