topic Re: [URGENT] Calculating a measure with Distinct Count for Maximum Date of Each Month in DAX Commands and Tips

[URGENT] Calculating a measure with Distinct Count for Maximum Date of Each Month

monak1 — Mon, 27 Nov 2023 07:52:27 GMT

Hi all , I want to show a trended graph on month year basis. I have table called 'my main table' which has OrderDate,orderid and status I want the distinctcount of orderid for maximum date of each month for opened status. below is the example of the data

OrderDate	OrderID	Status
11/27/2023	1	Opened
11/27/2023	2	Opened
11/27/2023	3	Opened
11/12/2023	1	Opened
11/12/2023	1	Opened
10/31/2023	1	Opened
10/31/2023	2	Opened
10/31/2023	3	Opened
10/31/2023	4	Opened
10/15/2023	1	Opened
10/5/2023	2	Opened
9/30/2023	1	Opened
9/30/2023	2	Opened
9/12/2023	1	Opened
9/11/2023	2	Closed
9/1/2023	4	Opened

I want the below as output from previous data

Month Year	Count	Status
Sep-23	2	Opened
Oct-23	4	Opened
Nov-23	3	Opened

please note that I want a measure and not a calculated column. I want to use this measure within another measure which is as below YourSecondMeasure = VAR MaxDate = MAX('YourDateTable'[Date]) VAR PreviousDates = DATESINPERIOD(SlicerTable[Date], MaxDate, -SelectPeriodValue, MONTH) VAR Result = CALCULATE( [Firstmeasure], REMOVEFILTERS('YourDateTable'[Date]), KEEPFILTERS(PreviousDates), USERELATIONSHIP('YourDateTable'[Date], SlicerTable[Date]) ) .

Below is the final output that I want

Please help ! thanks a lot in advance

Re: [URGENT] Calculating a measure with Distinct Count for Maximum Date of Each Month

FreemanZ — Mon, 27 Nov 2023 08:12:21 GMT

hi monak1 ,

not sure if i fully get yo, try to:

1) add a calculated dates table like:

dates = ADDCOLUMNS( CALENDAR(MIN(data[OrderDate]), MAX(data[OrderDate])), "YYMM", FORMAT([Date], "mmm-yy" ) )

2) relate dates[date] to data[date] as 1:N

3) plot a visual with dates[yy-mm] column and a measure like:

MonthEndDistinctCount = VAR _maxdate = MAX(data[OrderDate]) VAR _list = CALCULATETABLE( VALUES(data[OrderID]), data[OrderDate]=_maxdate, data[Status]="Opened" ) RETURN COUNTROWS(_list)

it worked like:

Re: [URGENT] Calculating a measure with Distinct Count for Maximum Date of Each Month

monak1 — Mon, 27 Nov 2023 08:53:48 GMT

thanks a lot for replying. It seems that I got the result . I used the below measure

measurename = VAR _maxdate = MAXX(
FILTER(
'my main table',
YEAR('my main table'[OrderDate]) = YEAR(MAX('my main table'[OrderDate])) &&
MONTH('my main table'[OrderDate]) = MONTH(MAX('my main table'[OrderDate])) &&
'my main table'[Status] = "Opened"
),
'my main table'[OrderDate]
)
RETURN
CALCULATE(
DISTINCTCOUNT('my main table'[OrderID]),
'my main table'[OrderDate] = _maxdate &&
'my main table'[Status] = "Opened"
)