Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
)
)
Thanks, Johnt75.
You are much appreciated.
Emoes
Instead of MAX( 'Dates'[Date]) try just using TODAY()
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
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
)
)