The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
)
)