DAX Time Intelligence and qualification

[jc508]

Hi,
I was using Power-BI Desktop (April 2018) to generate some DAX code via 'Quick Measure'
this was then to be included back in my SSAS tabular model (SSDT 17.4 build 14.0.61712.050)

TerminatedFTE := CALCULATE (
    sum('Turnover'[End Period Terminated FTE]),
    DATESBETWEEN(
      'Date'[Date].[Date],
      STARTOFMONTH(DATEADD(ENDOFMONTH('Date'[Date].[Date]), -11, MONTH)),
      ENDOFMONTH('Date'[Date].[Date])
    )
 )

However an error is generated in VS:-
"Warning        Measure 'Turnover'[TerminatedFTE] : Qualified name 'Date[Date].[Date]' is not allowed in this context"

I can find no reference to this style of extra qualification including other generated examples such as
          'Date'[Date].[Year],
          'Date'[Date].[QuarterNo],
          'Date'[Date].[Quarter],

What's going on?   Are there multiple dialects of DAX out there.

Thanks for any insight
JC

[Ashish_Mathur]

Hi,

Not sure of what you want but try this measure

=CALCULATE(sum('Turnover'[End Period Terminated FTE]),DATESBETWEEN('Date'[Date],EDATE(MIN('Date'[Date]),-11),MAX('Date[Date])))

In the visual, drag the date/month/year from the Date Table.

Hope this helps.

[jc508]

Thanks but I want to know what the triple qualification like 'Date'[Date].[Date] is based on and where it is documented.

And secondly why it only seems acceptable in Power-bi desktop

[Ashish_Mathur]

Hi,

I do not know.  I have never used that form.  Is my formula working?

[jc508]

Yes that formula works or at lease gives the same answer as my alternative

TerminatedFTE:= CALCULATE (
    sum('Turnover'[End Period Terminated FTE]),
    DATESBETWEEN(
      'Date'[Date],
      STARTOFMONTH(DATEADD(ENDOFMONTH('Date'[Date]), -11, MONTH)),
      ENDOFMONTH('Date'[Date])
    )
 )

which just stripped out the 3rd level qualification.

Going via SSAS also seems to have lost the auto date intelligence.