Measure returning data only when using smaller than operator

[jmkvalsund]

There might be an obvious solution for this topic, but I just dont see it:

I have a datasource looking like this:

CustomerID	OrderDateID	CAM
123456	20231215	CAM1
123457	20240120	CAM2
123458	20240115	CAM1
123459	20231227	CAM3
123460	20231120	CAM1

Then I want my measure to count the number of customers having their first order placed this year, using the MIN-function on OrderDateID,saying it should be larger than the measure M_LastDatePreviousYear. And then filter by CAM (Customer Account Manager).
Should be easy, like this:

M_NumberOfNewCustomersByCAMThisYear =

//Converting measure to variable
// (M_LastDatePreviousYear = 20231229, tested using both dynamical and static value)

VAR var_M_LastDatePreviousYear = [M_LastDatePreviousYear] 

RETURN CALCULATE(DISTINCTCOUNT(Customers[CustomerID]),
FILTER(Customers,MIN(Customers[OrderDateID])>var_M_LastDatePreviousYear),
FILTER(Customers[CAM]=[M_SelectedUSer])
)

However, this always returns a blank measure.
But if I change the operator like this:
FILTER(Customers,MIN(Customers[CreatedDateID])<var_M_LastDatePreviousYear),

it returns the number of customers having their first order before M_LastDatePreviousYear

I can also insert a table visual, and display all customers having their first order this year in the way shown above. So there are customers fulfilling my spec, I just want to count them 🙂

Anyone who can enlighten me on this one?

Regards,

John Martin

[jmkvalsund]

Hi @Anonymous ,

Thanks, I've tried your approach here, and I see this might be another way to solve the issue.
I can replicate the table and get the total numbers of new customers as total. 
However, if I then try to put M_NumberOfNewCustomersByCAMThisYear onto a card, it still says "(blank)"...
What's your properties on the card showing M_NumberOfNewCustomersByCAMThisYear?

Regards,
John Martin

[Anonymous]

Hi @jmkvalsund ,

You need to change :

MIN(Customers[OrderDateID])>var_M_LastDatePreviousYear

to :

CALCULATE ( MIN ( Customers[OrderDateID] ) ) > var_M_LastDatePreviousYear

in order for the card to output the correct result.

The card shows a total greater than the date 20231229.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @jmkvalsund ,

Firstly thanks to @Ashish_Mathur  and @lbendlin  for their prompt replies, I created some sample data to solve the problem for you. You can follow the steps below:

1. Modify your measure.

M_NumberOfNewCustomersByCAMThisYear =
VAR var_M_LastDatePreviousYear = [M_LastDatePreviousYear]
VAR M_SelectedUser =
    SELECTEDVALUE ( Customers[CAM] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Customers[CustomerID] ),
        FILTER (
            Customers,
            CALCULATE ( MIN ( Customers[OrderDateID] ) ) > var_M_LastDatePreviousYear
        )
    )

2.Add a Slicer.

Final output:
Count all CAM when OrderDateID > 20231229

Count CAM1 when OrderDateID > 20231229

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Based on the data that you have shared in the blue table, show the expected result.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...