Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |