question

devipatsa-3414 avatar image
0 Votes"
devipatsa-3414 asked devipatsa-3414 commented

SSAS Tabular Model: Calculate measure based on max Date in the selection

Hi Team,

I am currently building an SSAS tabular model based on a data model and quite new this space
. The fact table has point in time information based on the change date.
If the user has not selected the date, it should pick the sum of the amount against all other dimensions except for change date dimension where it should pick the latest change date value only.

Here is an example for better understanding.

If the fact table contains below.


117005-temp.png

If user selects Category and Sales amount, it should do the sum for all the measure except for Change date as below.
117014-temp1.png


sql-server-analysis-services
temp.png (7.8 KiB)
temp1.png (2.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered devipatsa-3414 commented

You could do something like the following, grouping by Category and Product, then calculating the max date and summing the amount. If your data set is not too large it should work OK. But it might not scale well on a large data set.

Sales Total =
SUMX( GROUPBY( 'Table', 'Table'[Category], 'Table'[Product]),
var maxDate = CALCULATE(max('Table'[Changedate]))
return CALCULATE(SUM('Table'[SalesAmount]), FILTER( values('Table'[Changedate]), 'Table'[Changedate] = maxDate))
)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Darren,
Thanks for your response.. the dataset will be quite huge..
Also, i am looking at another scenario where the same sale amount is used along with changedate and it should still display all the change dates along with reapective sales amounts.
Is this achievable?
Thanks
Devi

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered devipatsa-3414 commented

Hi @devipatsa-3414,

Welcome to Microsoft Q&A!

According to my knowledge, you could add FILTER function, please refer to Using the FILTER Function in DAX to get more information.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi carrin,
thanks for your response here.
Can this filter function be used in 2 scenarios like below?
1) when a change date is not selected, provide the sum of max date
2) when change date is used, show all the availablr dates along with its respective values
Thanks
Devi

0 Votes 0 ·