Hi I created a standard report which give me a monthly overview of the accounts receivables. Here I need to calculate difference in days between the receipt date and the reporting date.
For the reporting date I created a measure which tells me the last date of the previous month, since the report is created at the begininng of each month.
=Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1))
For the days between receipt date and reporting date I created a calculated column.
=Duration.Days([Reportingdate] - [Receiptdate])
it works as desired. The problem now is that I would like to make the reporting date dynamic, so that the reporting date can be change and everything should be recalculated again. I´m not sure if the use of parameters for this problem is correct, or I should use variables. I tried to use variables by using the formula above for the reporting date but i got an error. How should I do that?
Thanks in advance for your help.=)
Hi Maria,
The best solution will depend if you want the dynamic reporting date based on data already in the dataset, or a date you enter. If you want to enter it then you can use the Parameter Tables technique in session 7.01.
If you get stuck, please share a sample file or at least screenshots of the error and related information.
Mynda
Hi Mynda,
Thanks for the Reply. I was not sure how to use the Parameters in Formulas but I think I did it. Could you please check it. I attached a sample file.
Thank you very much.
Maria
Hi Maria,
No file is attached. You need to click the Start Upload button - instructions can be found here
https://www.myonlinetraininghub.com/excel-forum/forum-rules-and-guides/read-this-first
Regards
Phil
Hi Phil,
I tried again. Thanks!
Hi Maria,
When I open the file I get an error for the Custom column formula because the data type for the receipt date and due dates are date/time. I changed them to just date data type and the formula calculated and appears correct to me.
Mynda
oh God! beginner´s mistake!
Thank you for your help and apologies for wasting your time on something like this.
