DAX help - Calendar Help

[Dean1972]

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())

[v-jiascu-msft · Accepted 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