Forum Discussion

Dean1972's avatar
Dean1972
Frequent Visitor
8 years ago
Solved

DAX help - Calendar Help

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

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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