topic Trying to make a formula conditional by date depending on time from current date in DAX Commands and Tips

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

Anonymous — Fri, 08 Jul 2022 13:50:30 GMT

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."

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

johnt75 — Fri, 08 Jul 2022 15:04:29 GMT

You should be able to use

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

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

Anonymous — Fri, 08 Jul 2022 15:21:54 GMT

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

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

johnt75 — Fri, 08 Jul 2022 15:29:05 GMT

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

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

Anonymous — Fri, 08 Jul 2022 15:36:43 GMT

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.