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 dif...
Need Help
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.

     

    • jayt93's avatar
      jayt93
      Frequent Visitor
      6 years ago

      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