Add extra option in Dates period Union

[Emoes]

Hello all,

I'm trying to ADD an extra value (last 7 days) in my selection slicer.

With success I'm using the following:

Date Periods = 
UNION(
    ADDCOLUMNS(
        DATESMTD('Dates'[Date]),
        "Type", "MTD",
        "Sort", 1
    ),
    ADDCOLUMNS(
        DATESQTD('Dates'[Date]),
        "Type", "QTD",
        "Sort", 2
    ),
    ADDCOLUMNS(
        DATESYTD('Dates'[Date]),
        "Type", "YTD",
        "Sort", 3
    ),
    ADDCOLUMNS(
        PREVIOUSMONTH(DATESMTD('Dates'[Date])),
        "Type", "Last Month",
        "Sort", 4
    ),
    ADDCOLUMNS(
        PREVIOUSQUARTER(DATESQTD('Dates'[Date])),
        "Type", "Last Qtr",
        "Sort", 5
    ),
    ADDCOLUMNS(
        PREVIOUSYEAR(DATESYTD('Dates'[Date])),
        "Type", "Last Year",
        "Sort", 6
    ),
    ADDCOLUMNS(
        CALENDAR(MIN('Dates'[Date]),MAX('Dates'[Date])),
        "Type", "All Time",
        "Sort", 7
    ),
    ADDCOLUMNS(
        CALENDAR(MIN('Dates'[Date]),MAX('Dates'[Date])),
        "Type", "Custom",
        "Sort", 8
    )
)

I'n my simple mind, I thought to add: 

     ADDCOLUMNS(
        DATESINPERIOD(
        'Dates'[Date],
        MAX('Dates'[Date]),
        -7,
        DAY
    )
        "Type", "Last 7 days",
        "Sort", 0
    ),

So I'm able to show also the last 7days (the last date in the dates table is today)

What am I doing wrong?

Thanks,

Emoes

[Emoes]

Thanks, Johnt75.

You are much appreciated.

Emoes

[johnt75]

Instead of MAX( 'Dates'[Date]) try just using TODAY()

[Emoes]

Hello Johnt75,

Thanks for your reply.

I tried the following:

Date Periods = 
UNION(
    ADDCOLUMNS(
        DATESINPERIOD('Dates'[Date]),
	TODAY(),
	-7,
	Day
        "Type", "-7 Days",
        "Sort", 0
    ),
    ADDCOLUMNS(
        DATESMTD('Dates'[Date]),
        "Type", "MTD",
        "Sort", 1
    ),
    ADDCOLUMNS(
        DATESQTD('Dates'[Date]),
        "Type", "QTD",
        "Sort", 2
    ),
    ADDCOLUMNS(
        DATESYTD('Dates'[Date]),
        "Type", "YTD",
        "Sort", 3
    ),
    ADDCOLUMNS(
        PREVIOUSMONTH(DATESMTD('Dates'[Date])),
        "Type", "Last Month",
        "Sort", 4
    ),
    ADDCOLUMNS(
        PREVIOUSQUARTER(DATESQTD('Dates'[Date])),
        "Type", "Last Qtr",
        "Sort", 5
    ),
    ADDCOLUMNS(
        PREVIOUSYEAR(DATESYTD('Dates'[Date])),
        "Type", "Last Year",
        "Sort", 6
    ),
    ADDCOLUMNS(
        CALENDAR(MIN('Dates'[Date]),MAX('Dates'[Date])),
        "Type", "All Time",
        "Sort", 7
    ),
    ADDCOLUMNS(
        CALENDAR(MIN('Dates'[Date]),MAX('Dates'[Date])),
        "Type", "Custom",
        "Sort", 8
    )
)

This still gives an error:

The syntax for '"Type"' is incorrect. (DAX(UNION( ADDCOLUMNS( DATESINPERIOD('Dates'[Date]), TODAY(), -7, Day "Type", "-7 Days", "Sort", 0 ), ADDCOLUMNS( DATESMTD('Dates'[Date]), "Type", "MTD", "Sort", 1 ), ADDCOLUMNS( DATESQTD('Dates'[Date]), "Type", "QTD", "Sort", 2 ), ADDCOLUMNS( DATESYTD('Dates'[Date]), "Type", "YTD", "Sort", 3 ), ADDCOLUMNS( PREVIOUSMONTH(DATESMTD('Dates'[Date])), "Type", "Last Month", "Sort", 4 ), ADDCOLUMNS( PREVIOUSQUARTER(DATESQTD('Dates'[Date])), "Type", "Last Qtr", "Sort", 5 ), ADDCOLUMNS( PREVIOUSYEAR(DATESYTD('Dates'[Date])), "Type", "Last Year", "Sort", 6 ), ADDCOLUMNS( CALENDAR(MIN('Dates'[Date]),MAX('Dates'[Date])), "Type", "All Time", "Sort", 7 ), ADDCOLUMNS( CALENDAR(MIN('Dates'[Date]),MAX('Dates'[Date])), "Type", "Custom", "Sort", 8 )))).

Thanks again, Emoes

[johnt75 · Accepted solution]

You've got a close bracket in the wrong place

Date Periods =
UNION (
    ADDCOLUMNS (
        DATESINPERIOD ( 'Dates'[Date], TODAY (), -7, DAY ),
        "Type", "-7 Days",
        "Sort", 0
    ),
    ADDCOLUMNS ( DATESMTD ( 'Dates'[Date] ), "Type", "MTD", "Sort", 1 ),
    ADDCOLUMNS ( DATESQTD ( 'Dates'[Date] ), "Type", "QTD", "Sort", 2 ),
    ADDCOLUMNS ( DATESYTD ( 'Dates'[Date] ), "Type", "YTD", "Sort", 3 ),
    ADDCOLUMNS (
        PREVIOUSMONTH ( DATESMTD ( 'Dates'[Date] ) ),
        "Type", "Last Month",
        "Sort", 4
    ),
    ADDCOLUMNS (
        PREVIOUSQUARTER ( DATESQTD ( 'Dates'[Date] ) ),
        "Type", "Last Qtr",
        "Sort", 5
    ),
    ADDCOLUMNS (
        PREVIOUSYEAR ( DATESYTD ( 'Dates'[Date] ) ),
        "Type", "Last Year",
        "Sort", 6
    ),
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Dates'[Date] ), MAX ( 'Dates'[Date] ) ),
        "Type", "All Time",
        "Sort", 7
    ),
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Dates'[Date] ), MAX ( 'Dates'[Date] ) ),
        "Type", "Custom",
        "Sort", 8
    )
)