Passing User Inputted Date on Visual Level to Data Level

[jayt93]

Hi everyone,

New to PowerBI and I'm having some trouble figuring out how to approach this:

I have a set of data which includes Accounts Receivable Invoices and Payments on the invoices on different lines.

I have an Aging Summary that uses a calculated column 'AR Age':

AR Age = today()-'AR Query'[Invoice Date]

Then I used another calculated column to categorize the age:

AR Aging = if('AR Query'[Status]="Paid","Paid",IF('AR Query'[AR Age]>=0 && 'AR Query'[AR Age]<=30,"Current",

IF('AR Query'[AR Age]>=31 && 'AR Query'[AR Age]<=60,"31-60 Days",

IF('AR Query'[AR Age]>=61 && 'AR Query'[AR Age]<=90,"61-90 Days",">90 Days"))))

Is there any way to pass a user inputted date (through a slicer perhaps) on the visual level to the calculated column for 'AR Age' instead of using today() .

Any help would be much appreciated!

[mwegener]

Hi @jayt93 ,

create a data table without relation.

Input Date = CALENDAR (DATE (2015, 1, 1), DATE (2020, 12, 31))

AR Age = MAX('Input Date'[Date])-'AR Query'[Invoice Date]

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support. 

[jayt93]

Thank you, worked perfectly! 

@mwegener 

Sorry I spoke to soon, I set up the table as you said and there was no errors with the calculation.

However, when I set up a slicer based on the input date table on the visual level and adjust it, it doesn't seem to be affecting the AR Age column. The AR age column seems to be always taking the max date set in the input date table.

Am I missing any steps to have the filter work properly?

Thanks

[mwegener]

Hi @jayt93 .

Sorry, that was my mistake.

Calculated columns are also calculated when loading data.

We need to create a measure...

AR Age = MAX('Input Date'[Date])- MAX('AR Query'[Invoice Date])

... for the further calculation I would have to know the data model / report.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[jayt93]

I ended up just using a measure as you said and was able to produced the desired results on the visual level without needing to display it in the data level via a calculated column anyways. 

In case anyone is working on something similar, this is one of the measures I used:

>61-90 Days = calculate(sum('AR Query'[Base Amt]),filter('AR Query',(max('Input Date'[Date])-'AR Query'[Invoice Date]>60) &&

(max('Input Date'[Date])-'AR Query'[Invoice Date]<=90)))

But just out of curisoity still, is there any way for me to filter the input date on the slicer and pass that date into a calculated column in the data level?

Thanks

[mwegener]

Hi @jayt93,

has your question been answered?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener · Accepted solution]

Hi @jayt93 ,

Unfortunately, No.

I recommend this free DAX video training.

https://www.sqlbi.com/p/introducing-dax-video-course/

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.