topic Dates between giving unexpected response in DAX Commands and Tips

Dates between giving unexpected response

Anonymous — Thu, 02 Nov 2023 09:39:17 GMT

Good morning, I have a table with a column with continuous dates between today and 2 years ago.

I am trying to use the code below to create a table with time periods. My intent is to create a column where dates from today to 1 year ago are given a label, and dates from 1 year ago to 2 years, are given another label.

My code returns a blank table.

Can someone point me in the right direction on how to fix this?

Date Periods =

UNION(

ADDCOLUMNS(

DATESBETWEEN(MyDate[Date],TODAY(),TODAY()-365),

"Type", "Asset 1 Year"

ADDCOLUMNS(

DATESBETWEEN(MyDate[Date],TODAY()-366,TODAY()-731),

"Type", "Vendor 2 Year"

)

I look forward to hearing back from you.

Re: Dates between giving unexpected response

BeaBF — Thu, 02 Nov 2023 09:50:09 GMT

Anonymous Hi!

Try with:
Date Periods =
UNION(
ADDCOLUMNS(
FILTER(MyDate, MyDate[Date] >= TODAY() && MyDate[Date] < TODAY() - 365),
"Type", "Asset 1 Year"
),
ADDCOLUMNS(
FILTER(MyDate, MyDate[Date] >= TODAY() - 365 && MyDate[Date] < TODAY() - 730),
"Type", "Vendor 2 Year"
)
)

BBF

Re: Dates between giving unexpected response

Anonymous — Thu, 02 Nov 2023 10:02:29 GMT

BeaBF

Thank you for the response, once again I get a blank table.

here is the code for my date table if that helps, it is just dates, unsummarized, formatted in DD/MM/YYYY (Short Date)

MyDate =

CALENDAR(

min(Historic1[Date]),

MAX(Historic1[Date])

)

Ive done similar things before but using previousqtr or previous month in place of the datesinbetween, but this seems to not work. I apprecaite the response, thank you

Lola

Re: Dates between giving unexpected response

tamerj1 — Thu, 02 Nov 2023 10:39:55 GMT

Hi Anonymous
Please try

Date Periods = ADDCOLUMNS ( CALENDAR ( TODAY () - 730, TODAY () ), "Type", IF ( [Date] < TODAY () - 365, "Vendor 2 Year", "Asset 1 Year" ) )

Re: Dates between giving unexpected response

Anonymous — Thu, 02 Nov 2023 10:54:24 GMT

tamerj1 Thank you for your time in getting this for me. Will this table only hold 2 years worth of dates and each new day, the table will recalculate itself and the last date disappears?

Lola

Re: Dates between giving unexpected response

tamerj1 — Thu, 02 Nov 2023 12:34:28 GMT

Anonymous
Exactly