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.
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
Solved! Go to Solution.
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"
)
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:
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"
)
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 |
---|---|
44 | |
28 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |