Distinct Count values for the whole time period not for each month

[Krexx]

I would like to display the amount of productnumbers per month for two different status (In Order, Ordered) based on slicer time period.

For this i have created an measure where i want to display the amount of productnumber per month. I am using following measure: 

ProductCount = 
VAR EndDate = max(DATUM[Orderdate])
Var Startdate = min(DATUM[Orderdate])
VAR FilterDate = DATE(YEAR(Startdate),MONTH(Startdate)-6, DAY(Startdate))
VAR Result = 
    CALCULATE(
        DISTINCTCOUNT('Order_Version'[Productnumber]),
        FILTER(
            'Order_Version',
            'Order_Version'[Orderdate] >= FilterDate&& 'Order_Version'[Orderdate] <= EndDate && 'Order_Version'[Status] = "In Order" || 
            'Order_Version'[Orderdate] >= Startdate && 'Order_Version'[Orderdate] <= EndDate && 'Order_Version'[Status] = "Ordered"))
Return
Result

When i am using this measure in a barchart, it uses this measure for each month seperately, but i would like to count distinct based on the whole time period.

So for example if productnumber "111AAA2-X" with Status "In Order" is available on

19th June 2019
20th October 2019
01st January 2020

Then it should only count this Productnumber in June 2019. With the actual measure it displays the productnumber "111AAA2-X" in each of these 3 months.

Here is an example of my table: 

106000-N	In Order	20.03.2019
106000-N	In Order	25.06.2019
106000-N	In Order	27.09.2019
106000-N	Ordered	08.02.2019
106000-N	Ordered	08.04.2019
106122-N	In Order	24.01.2019
106122-N	In Order	28.02.2019
106122-N	Ordered	08.02.2019
106124-N	In Order	24.01.2019
106124-N	In Order	28.05.2019
106124-N	Ordered	11.02.2019
106124-N	Ordered	11.05.2019

In the table above only the bold once should be counted. I was trying to work with earlier function, but it doesn work in a measure. Does anyone knows how to solve it?

Thanks in advance

[v-xicai]

Hi @Krexx   ,

Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[Ashish_Mathur]

Hi,

So what final result are you expecting.  Is it 6 in a card visual or do you want to see the 6 rows in a Table visual?  Please show the expected result very clearly.

[Krexx]

@dedelman_clng thanks for the hint. Unfortunately the results are the same.
@amitchandak your function doesnt work there are Syntax after the second CALCULATE Function
@Ashish_Mathur i will share a picture of what i expect:

For the Productnumber "158820-N" i would expect to be only counted in May 2020. But its counted also in June 2020. I want that the Distinct Count on Productnumber is based on the whole time period, but actualy the distinctcount function works based on each month.

[amitchandak]

@Krexx , Assuming it across month have a measure like this in visual in place of date and try

Measure =
VAR __id = MAX ( 'Table'[productnumber] )
VAR __date = CALCULATE ( MIN( 'Table'[Date] ), ALLSELECTED ( 'Table' ), 'Table'[productnumber ] = __id )
CALCULATE ( Min ( 'Table'[Date] ), VALUES ('Table'[productnumber ] ), 'Table'[productnumber ] = __id,'Table'[Date] = __date )

[Krexx]

@amitchandak your measure works, my fault i forgot to set another variable for the final Calculation. Now i got the right min(date) for each Productnumber. Is there any way i can use this measure for my x-axis to display the months?

[v-xicai]

Hi @Krexx ,

A measure is unsupported to be used in Axis of chart, you may create a column like DAX below.

MinDate =
CALCULATE (
    MIN ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[productnumber ] )
)

Best Regards,

Amy 

Community Support Team _ Amy

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

[dedelman_clng]

Hi @Krexx  - you have a logic error in your code.  The || (or) is not separating the two && (and) clauses.

Try 

ProductCount = 
VAR EndDate = max(DATUM[Orderdate])
Var Startdate = min(DATUM[Orderdate])
VAR FilterDate = DATE(YEAR(Startdate),MONTH(Startdate)-6, DAY(Startdate))
VAR Result = 
    CALCULATE(
        DISTINCTCOUNT('Order_Version'[Productnumber]),
        FILTER(
            'Order_Version',
            ('Order_Version'[Orderdate] >= FilterDate &&
               'Order_Version'[Orderdate] <= EndDate && 
                  'Order_Version'[Status] = "In Order" ) || 
            ( 'Order_Version'[Orderdate] >= Startdate && 
                  'Order_Version'[Orderdate] <= EndDate && 
                     'Order_Version'[Status] = "Ordered" )
        )
)
Return
Result

 Hope this helps

 David