Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I want to get the customer keys for the last 12 months from the date selected from the slicer. I have tried the following measure-
Customer ttm =
I have used a disconnected date table for this model.
Snap of error
Can anyone help me out? Any suggestion? Thanks in advance.
Solved! Go to Solution.
Hi @freakinaditya,
Values and distinct functions can return multiple values that measure formula not supported. I think you need to do some additional options to prevent the multiple result issue. You can try to use the following measure formula if it was suitable for your requirement.
Customer ttm =
VAR CurrentDate =
MAX ( 'Date'[End of Month Date] )
RETURN
CALCULATE (
CONCATENATEX ( VALUES ( FactInternetSales[CustomerKey] ), [CustomerKey], "," ),
FILTER (
FactInternetSales,
FactInternetSales[End of Month Date]
>= DATE ( YEAR ( CurrentDate ) - 1, MONTH ( CurrentDate ), DAY ( CurrentDate ) )
&& FactInternetSales[End of Month Date] <= CurrentDate
)
)
If above expression not helps, please share more detailed information to help us clarify your data structure and do test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @freakinaditya,
Values and distinct functions can return multiple values that measure formula not supported. I think you need to do some additional options to prevent the multiple result issue. You can try to use the following measure formula if it was suitable for your requirement.
Customer ttm =
VAR CurrentDate =
MAX ( 'Date'[End of Month Date] )
RETURN
CALCULATE (
CONCATENATEX ( VALUES ( FactInternetSales[CustomerKey] ), [CustomerKey], "," ),
FILTER (
FactInternetSales,
FactInternetSales[End of Month Date]
>= DATE ( YEAR ( CurrentDate ) - 1, MONTH ( CurrentDate ), DAY ( CurrentDate ) )
&& FactInternetSales[End of Month Date] <= CurrentDate
)
)
If above expression not helps, please share more detailed information to help us clarify your data structure and do test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Yes, I am using it as a measure.
I have to actually get actual values of customer keys not to count the total numbers of customer keys.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |