Power Query: Dynamic Parameters

[Bob_W]

HI All - as a novice Power Query user I'm hoping this should be an easily answered question, but would really appreciate your assistance none the less.

I am looking to create some dynamic parameters which can be used as part of a calculation in a custom column. This will be used in a cash report to show the forecast past due position at Quarter End by calculating how many additional days there are to QE and adding this to the current ageing so that the revised aging can be displayed.

I would like to create three parameters:

1. "Current_Rpt_Date" which would be today. Equivalent to the Excel TODAY() function.

2. "Next_QE_Date" which would take today's date and calculate the next quarter end date. In Excel I would use:

=DATE(YEAR(TODAY()),CEILING(MONTH(TODAY()),3)+1,1)-1

3. "System_Rpt_Date" which would capture a date value from a specific cell in dim table used in the query

Thanks in advance

 

[alansidman]

It would be really helpful if you uploaded your sample source data (8-10) records representative of your actual data. Identify which field you wish to filter with a parameter. Please use XL2BB to upload your data so that we do not need to recreate your existing data.