Trying to make a formula conditional by date depending on time from current date

[Anonymous]

Hi all,

I am trying to implement the Excel formula in the below screenshots into DAX for use in a Power BI dashboard.

Here is my data, the formula, and an explanation of what the formula does:

If a date is over 6 days ago, I do not want to adjust its value. If a date is within 6 days of the current date but more than 2 days ago (so 3, 4, 5, or 6 days ago), I want to increase it by 11.5% each day for the number of days remaining until it hits 6 days. If a date was within the past 2 days, I want to increase it by 13% each day for the number of days remaining until it hits 6 days.

The formula shown below accomplishes that in Excel.

I am now trying to port the formula into Power BI. I used a formula identical to the one above, but I replaced DAYS() with DATEDIFF(). However, I get an error when trying to reference the date column, saying that "A single value for column 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[johnt75]

You should be able to use

DATEDIFF( TODAY(), SELECTEDVALUE('Table'[Date]), DAY)

[Anonymous]

Thank you for the help. I just tried this and am no longer getting the same error, but it did cause a new issue. The results are instead blank for the new measure in the table I made

[johnt75]

can you post the full measure definition and maybe a screenshot of the table structure?

[Anonymous · Accepted solution]

I just realized the issue; I had missed part of my formula, so the nested IF statement was off. It works now! Thank you so much; I massively appreciate it.