February 15, 2017 at 9:28 am
I'm having a heck of a time finding information about this. Here's the scenario:
Let's say you have an SSAS model setup with the following measures:
Total Sales:=sum(sales)
Total Sales MTD:= totalmtd(Total Sales, 'date'[date])
Total Sales MTD (Prior Year):= calculate([Total Sales MTD], SamePeriodLastYear('date'[date]))
How can I write a DAX query to return these three measures for say 12/15/2016. I did something like this:
evaluate
(
calculatetable
(
summarize
(
'factTable',
'Date'[Calendar Year],
"Total Sales", 'factTable'[Total Sales],
"Total Sales MTD", 'factTable'[Total Sales MTD],
"Total Sales MTD (Prior Year)", 'factTable'[Total Sales MTD (Prior Year)]
),
'Date'[Date] = date(2015, 12, 15)
)
)
and what I get back are the sales for that specific day (Total Sales are showing equal to Total Sales MTD) and the prior year value is blank. I tried playing around with the filter and can never get anything but a blank for that prior year column. There's a lot of info out there about creating DAX formula's, but doesn't seem like there is a lot for writing full queries. Any advice is much appreciated!
February 15, 2017 at 9:37 am
I should also add, I wrote the query this way:
evaluate
(
filter
(
summarize
(
'factTable',
'Date'[Calendar Year],
'Date'[Full Date],
"Total Sales", 'factTable'[Total Sales],
"Total Sales MTD", 'factTable'[Total Sales MTD],
"Total Sales MTD (Prior Year)", 'factTable'[Total Sales MTD (Prior Year)]
),
'Date'[Full Date] = date(2016, 12, 15)
)
)
But this query takes over a minute to run, I'd expect to get this back from the model almost instantly. I can right an MDX query against the model and it returns wha I'm looking for in under a second.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply