How to add a date parameter column to a query

[jkdt33]

I am trying to be able to create a parameter that adds a 'X' # of days to a date for accounting. But the problem is that I run into is that I need to then be able to connect this new date to my date table in order to be able to return the week # of this new dated parameter. 

I need to be able to add a different days past a due date based on my needs. The organization wants to be able to look and see when something is going to hit 'x' amount of days from its original due date. 

For Example. I have a due date column that originally shows a due date of 7/1/2023 ('Fact', 'Fact'[Due_Date]). I have created a parameter measure that allows me to add 'x' # of days from that original due date. So in my Number of Days Past Due Date'[Value] paramenter, if i input 90, it will return 9/29/2023, or if I input '200' it will return 1/17/2024. 

My Problem: I need to be able to return what week # the ''X' Days after Due Date Variable' measure returns. I have a date table that gives me my week numbers, but I am not able to create a relationship from my date table to my Variable measure to return the week #. any help? 

'X' Days after Due Date Variable =

VAR DueDate = SUMX('Fact', 'Fact'[Due_Date])  

VAR DateVariable = SELECTEDVALUE('Number of Days Past Due Date'[Value] )

Return DueDate + DateVariable

[jkdt33]

So here is an exmaple of some of my Data. I have a Due_date Column. I need to be able to have a variable parameter that allows me to change the # of days AFTER the due_date. Once I have this 'X' Days after Due Date Variable, I need to be able connect that date to my date column. I need to try to show what 'Week' that 'X' Days after Due Date Variable falls into and make my visuals based on that data. But I am unable to due that right now because I have a DAX Measure calculating the date after due date based on my parameter. 

[jkdt33]

[Anonymous]

Hi @jkdt33 ,

Below is my table:

The following DAX might work for you:

X days after due date = 
    var dat = SELECTEDVALUE('Table'[Due_Date])
    return
       dat+'Days after due date'[Days after due date Value]

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jkdt33]

Ill try to re explain my needs better to better understand the entirety of what I am looking for and needing. 

I have 2 total tables. An accounting fact table and a Date table. 

Accounting Fact Table 

- I have a column that has a outstanding balance 

- I have a Colmn that has the original Due_Date for that balance 

- My Task is i need to have a variable/parameter day to be able to change how many ('X') days is past the original due date. So we want to know when is 90 days, 150 days, 200 days, etc. past the due date

- I need to have this 'Variable Date' to be in a column so that I can use this column to be my relationship date with my Date Table 

- I need to create my visuals based on the Date of the variable date column. 

 

[Anonymous]

Hi @jkdt33 ,

Can you provide us with the sample data and the expected results? Thank you

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jkdt33]

Or my other thought is how can I be able to create a new column where I can add my selected parameter # and add it to my due_date column by row? Or convert my measure to be able to be a column. 

I dont have extensive knowledge in M query, but I have attempted it that way as well and it will not allow me to at a number to a date format.