cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jc508 Frequent Visitor
Frequent Visitor

DAX Time Intelligence and qualification

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

4 REPLIES 4
Super User
Super User

Re: DAX Time Intelligence and qualification

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 Frequent Visitor
Frequent Visitor

Re: DAX Time Intelligence and qualification

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

 

 

Super User
Super User

Re: DAX Time Intelligence and qualification

Hi,

 

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

jc508 Frequent Visitor
Frequent Visitor

Re: DAX Time Intelligence and qualification

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.