Forum Discussion

markmess77's avatar
markmess77
Resolver I
5 years ago

Unable to use time intelligence function when creating table with DAX?

I'm trying to create a new table of time intelligence calculations, but it does not seem to be working. I am using the SUMMARIZE function to groupby three different columns and perform the time intelligencence calculations.

If I try something like:

SUMMARIZE( 
    Table,
    'Date'[Date], Table[Category], Table[Subcategory],
    "Last Year",  CALCULATE(COUNT('Table'[Subcategory]), DATEADD('Date'[Date], -1, YEAR))
)

 The groupby works fine, but I do not return any values for my "Last Year" column - my result would look something like:

However, the same time intelligence calculation works perfectly fine as a measure.. just not in this context?

Additionally if I try another expression in place of the time intelligence calculation, I get the result I would expect.

For example, the following works perfectly fine with no blank values:

SUMMARIZE( 
    Table,
    'Date'[Date], Table[Category], Table[Subcategory],
    "Last Year",  COUNT('Table'[Subcategory])
)

I'm not quite sure why this works, but the first one doesn't... especially when the same time intelligence expression works perfectly fine as a stand-alone measure.

Is there anything I can do to modify my expression to get this to work?

2 Replies