Forum Discussion

jayt93's avatar
jayt93
Frequent Visitor
6 years ago
Solved

Passing User Inputted Date on Visual Level to Data Level

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!
 
 
Need Help

6 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    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's avatar
      jayt93
      Frequent Visitor
      6 years ago

      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's avatar
        mwegener
        Most Valuable Professional
        6 years ago

        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.