Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Please could i ask for your help,
I have created tables for all the other columns but keep getting a issues the created filed????
Info:
Table One
PRJSTRequests Created =
UNION (
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('PRJSTRequests' , DATESBETWEEN('PRJSTRequests'[Created],today()-07+1,today()) ),
'PRJSTRequests'[Created]),"Period","Last 07 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('PRJSTRequests' , DATESBETWEEN('PRJSTRequests'[Created],today()-14+1,today()) ),
'PRJSTRequests'[Created]),"Period","Last 14 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('PRJSTRequests' , DATESBETWEEN('PRJSTRequests'[Created],today()-30+1,today()) ),
'PRJSTRequests'[Created]),"Period","Last 30 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('PRJSTRequests' , DATESBETWEEN('PRJSTRequests'[Created],today()-60+1,today()) ),
'PRJSTRequests'[Created]),"Period","Last 60 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('PRJSTRequests' , DATESBETWEEN('PRJSTRequests'[Created],today()-90+1,today()) ),
'PRJSTRequests'[Created]),"Period","Last 90 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('PRJSTRequests'), 'PRJSTRequests'[Created]),"Period","Overall")
)
I get the following error
"A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This is not supported.The current operation was cancelled because another operation in the transaction failed."
Table Two - Linking table
PRJSTRequests Created Date = CALENDAR("09/10/2000",TODAY())
Solved! Go to Solution.
Hi @Dean1972,
As we can see from your formula, it has nothing to do with the column 'PRJSTRequests'[Created]. So we can create a independent Date table.
1. Create a date table.
Calendar = CALENDAR ( DATE ( 2017, 01, 1 ), DATE ( 2017, 12, 31 ) )
2. Create a new column of 'PRJSTRequests' due to there is a time part in the [Created].
NewCreated = [Created].[Date]
3. Try this formula. You can try it in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgS-yaLt1FR6zDe4w
PRJSTRequests Created = UNION ( ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 07 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 07 Days" ), ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 14 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 14 Days" ), ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 30 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 30 Days" ), ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 60 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 60 Days" ), ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 90 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 90 Days" ), ADDCOLUMNS ( VALUES ( 'PRJSTRequests'[NewCreated] ), "Period", "Overall" ) )
Best Regards!
Dale
Hi @Dean1972,
As we can see from your formula, it has nothing to do with the column 'PRJSTRequests'[Created]. So we can create a independent Date table.
1. Create a date table.
Calendar = CALENDAR ( DATE ( 2017, 01, 1 ), DATE ( 2017, 12, 31 ) )
2. Create a new column of 'PRJSTRequests' due to there is a time part in the [Created].
NewCreated = [Created].[Date]
3. Try this formula. You can try it in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgS-yaLt1FR6zDe4w
PRJSTRequests Created = UNION ( ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 07 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 07 Days" ), ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 14 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 14 Days" ), ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 30 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 30 Days" ), ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 60 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 60 Days" ), ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', DATESBETWEEN ( 'Calendar'[Date], TODAY () - 90 + 1, TODAY () ) ), 'Calendar'[Date] ), "Period", "Last 90 Days" ), ADDCOLUMNS ( VALUES ( 'PRJSTRequests'[NewCreated] ), "Period", "Overall" ) )
Best Regards!
Dale
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |