mixing calculated columns and measures

[RudyL]

Hi,

I hope this link (example_pbix_on_wetransfer ) still works. It has the pbix with my challenge.

I have a simplified invoices table (fig1). I linked the transdate with a datetable and added a slicer to the report on the date table. This way I managed to use the date table as a reference date to show only the invoices and payments before a certain date. I can then calculate the Openposition on a certain date (for example march-2-2020) through OpenPosition = Invoices[Invoiced]-Invoices[Paid], (see fig2).

I want to use the same reference date to determine the status of an invoice on the given date instead of using the NOW() to present the results. So I made a measure that returns the number of days from the invoicedate and the reference date (see fig2)

Due2Ref = var _max = maxx(allselected(TDates), TDates[RefDate].[Date])

return averageX(values(Invoices), datediff(max(Invoices[InvoiceDate]),_max,day))

But now the struggle begins. I want to see how much is unpaid  that should have been paid less than 60 days ago and how much is unpaid that should have been paid more than 90 days ago, RELATIVE TO THE REFERENCE DATE!

It’s straightforward to use a calculated column, and this one works when I use NOW() as a reference date. (fig2). But I don’t want the opendays calculated from NOW() but from the reference date. And then the mixing of measures and calculated columns begins. How do I change the open0-60 column that it uses the reference date?

Open0-60 = if(now() >= Invoices[InvoiceDate].[Date] && now()<=Invoices[InvoiceDate].[Date]+60,Invoices[OpenPosition],0)

In the fig2-example 200 from Invoice I1 should remain on open60plus, but 200+1000 from I2 and I3 should move to open0-60 and the total open0-60 should become 1200 while total open60 plus would be 200.

fig1.

fig2

[amitchandak · Accepted solution]

@RudyL , Please check something like this can work?

Due2Ref = var _max = maxx(allselected(TDates), TDates[RefDate].[Date])

return averageX(filter(values(Invoices),datediff(max(Invoices[InvoiceDate]),_max,day)<=60), datediff(max(Invoices[InvoiceDate]),_max,day))

Or you can think of dynamic segmentation https://www.youtube.com/watch?v=CuczXPj0N-k&t=398s

[RudyL]

Hi,

Almost. I changed your suggestion to 

 Op0-60 = var _max = maxx(allselected(TDates), TDates[RefDate].[Date])

return sumX(filter(values(Invoices),datediff(max(Invoices[InvoiceDate]),_max,day)<=60), Invoices[OpenPosition])

and the same for op60plus to get an almost perfect result. It adds the right numbers on a line level, but in the totals the measures go out of control and they add 1000+200 to 1400 and 200+0 to blank. Is there some solution to make the totals correct as well?

[RudyL]

Hi @amitchandak ,

Correction. My bad. I reported a remaining issue but that was because I had an error in my measure. 

Op0-60 = var _max = maxx(allselected(TDates), TDates[RefDate].[Date])

return sumX(filter(values(Invoices), datediff(max(Invoices[InvoiceDate]), _max,day)<=60), Invoices[OpenPosition])

should have been

Op0-60 = var _max = maxx(allselected(TDates), TDates[RefDate].[Date])

return sumX(filter(values(Invoices), datediff(Invoices[InvoiceDate], _max,day)<=60), Invoices[OpenPosition])

note the removed MAX() in the first datediff parameter. Now it works like a charm!! Thank you for your help.