Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
)
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
125 | |
32 | |
27 | |
24 | |
23 |