SSAS - Issue with Calculated Table

[rsbin]

Hello,

I am trying to create a "Time Period Slicer" using the techniques demonstrated by Phil Seamark and Patrick at Guy in a Cube.

I have come up with the following code which I have been able to get to work in a sample PBIX file.

Time Intelligence 2 = 
VAR _today = MAX( FACT_OperatorStatistics_ALL_DALY[WIDate])
VAR _thisyear = YEAR( _today )
VAR _thismonth = MONTH( _today )
VAR _thisday = DAY( _today )
RETURN 
    SELECTCOLUMNS(
        UNION
            (
                ADDCOLUMNS(
                GENERATE(
                    SELECTCOLUMNS( {"Prior Day"}, "Period", [Value]),
                    GENERATESERIES(
                        DATE( _thisyear , _thismonth, _thisday - 1), _today - 1)),
                        "Axis Date", [Value] ),
                ADDCOLUMNS(
                GENERATE(
                    SELECTCOLUMNS( {"Last 7 Days"}, "Period", [Value]),
                    GENERATESERIES(
                        DATE( _thisyear , _thismonth, _thisday - 7), _today - 1)),
                        "Axis Date", [Value] ),
                ADDCOLUMNS(
                    GENERATE(
                    SELECTCOLUMNS({"Last 30 Days"},"Period",[Value]) ,
                    GENERATESERIES(
                            DATE( _thisyear, _thismonth, _thisday-30) ,
                            _today-1
                            )
                        ),
                        "Axis Date",[Value]
                    ),

                ADDCOLUMNS(
                GENERATE(
                    SELECTCOLUMNS({"Year To Date"},"Period",[Value]) ,
                    GENERATESERIES(
                        DATE( _thisyear , 1 , 1) , _today - 1 )),
                "Axis Date",[Value]),

                ADDCOLUMNS(
                    GENERATE(
                    SELECTCOLUMNS({"Prior Year"},"Period",[Value]) ,
                    GENERATESERIES(
                            DATE( _thisyear -1 , 1 , 1) ,
                            DATE(_thisyear -1, 12, 31 )
                            )
                        ),
                    "Axis Date", [Value] )),

        "Date" , [Value] ,
        "Period" , [Period] ,
        "Axis Date" , [Axis Date]
    )     

However, my model is using a Direct Query to SSAS.  When I copy this code into SSAS, it fails to generate the table.  It looks like I get an error at the first ( "Axis Date", [Value] ) line.   [Value] is underlined in red in the SSAS code.

I am a beginner with SSAS, so hoping one of you can help me understand the issue and possibly provide a correction.

Thanks in advance and Best Regards, 

[v-yalanwu-msft]

Hi, @rsbin ;

If you create a table in  AS  tabular-modes , you should note Select Table > New calculated table.  refer to the following article.

If it is not correct, can you share pbix or data after removing sensitive information?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rsbin]

Good Morning @v-yalanwu-msft,

Thank you for the reply.  I understand how to create a New Calculated Table.  The issue is with the DAX syntax in AS.  There seems to be some functions that SSAS does not like.  I was kinda hoping someone would be able to help me out in this regard.  My next thought is to create the table with only one or two columns first and then AddColumns as required.

Thanks again for taking the time to reply.

Kindest Regards,