Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
freakinaditya
Helper II
Helper II

How to get customer keys for last 12 months from selected date from slicer ?

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 =

VAR CurrentDate =
IF(
COUNTROWS(DISTINCT(ALLSELECTED('Date'[End of Month Date]))) = 1,
SELECTEDVALUE('Date'[End of Month Date]),
MAX('Date'[End of Month Date])
)
VAR PreviousDate = CurrentDate - 365
VAR Result =
CALCULATE(
DISTINCT(VALUES(FactInternetSales[CustomerKey])),
FILTER(
FactInternetSales,
FactInternetSales[End of Month Date] >= PreviousDate && FactInternetSales[End of Month Date] <= CurrentDate
)
)
Return Result
 
but the result is not coming. I am getting an error while displaying measure in table visual.

I have used a disconnected date table for this model.

 

Snap of errorSnap of error

 

Can anyone help me out? Any suggestion? Thanks in advance.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
AllisonKennedy
Super User
Super User

Are you trying to do this as a Measure?

The DISTINCT function returns an entire column of values. Try using DISTINCTCOUNT instead.

Please @mention me in your reply if you want a response.

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.

You cannot get more than 1 value in a Measure - measures must evaluate to 1 scalar number.

Try using the CONCATENATEX() function around the VALUES instead of Distinct.

Please @mention me in your reply if you want a response.

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.