Issue with Adding Days in Direct Query and Null Entries in a Date Column

[Anonymous]

All columns are underlined!!

So I have a query that has a list of customer orders. There is a Desired Ship Date column and I am trying to make a report that only displays orders within the next 90 days (updating daily). And every order that has a Desired Ship Date that is before today's date, will just appear as todays date.

This is what I have done:

1. I added a column (Today's Date) that listed today's date as every entry. It was a custom column (=DateTime.LocalNow()).

2. I added another column (Desired Ship Date After Today). It was a conditional column (If Desired Ship Date is before Today's Date then display Today's Date. Else if Desired Ship Date is after Today's Date then display Desired Ship Date)

3. I added a column (90 Days From Today) to show Today's Date plus 90 days. It was a custom column (=Date.AddDays[Today's Date]+90).

4. I added another column (Orders Within 90 Days). It was a conditional column (If Desired Ship Date After Today is before or equal to 90 Days From Today then display Desired Ship Date After Today.

So my first issue is with step 3 (90 Days From Today), as soon as I created this column I could no longer work in Direct Query. Is there a fix for this? Is there another way to sort between orders within 90 days rather than making this column?

My second issue is in step 4 (Orders Within 90 Days). The column worked, but any date that was in the future and not within 90 days became 'null'. And I don't know how I can remove these columns so I can make the visualisations, but also not delete the rows because as Today's Date changes more of these rows will be within 90 days.

And my final question is: is there an easier way to do all of this? I am new to Power BI and making 4 new columns seems like a very long process.

Thank you!!

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

You can go to report view,using dax expressions to realize it.

Create 2 measures as below:

90 Days From Today = TODAY()+90

Orders Within 90 Days = IF(MAX('Student'[sDate])>=TODAY()-90&&MAX('Student'[sDate])<=TODAY(),MAX('Student'[sDate]),BLANK())

Or you can create 2 calculated columns as below:

_90 Days From Today = TODAY()+90

_Orders Within 90 Days = IF('Student'[sDate]>=TODAY()-90&&'Student'[sDate]<=TODAY(),'Student'[sDate],BLANK())

Finally you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!