Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Dates between giving unexpected response

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.
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 
Please try

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

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

Hi @Anonymous 
Please try

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

@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

@Anonymous 
Exactly

BeaBF
Memorable Member
Memorable Member

@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

Anonymous
Not applicable

@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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors