Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
@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.
@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 )
@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?
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |