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,
I’m trying to build a matrix that will count the number of services I have that are active as at the close of each month.
For example we sell subscription services that have a Connected date and a Disconnected date, what I am trying to do is count the number of active services as at the end of each month.
To determine an active service the Connection date must be prior to the end of the month and the disconnection date was greater than the end of the month.
In terms of structure, each row of the table stores the unique Service ID along with the Connected date and Disconnected date on the same row.
Can anyone help.
Cheers
Todd
Solved! Go to Solution.
Hi @ToddMate ,
My original data:
First, I create a table with DAX:
Table = CALENDAR(DATE(2019, 5, 1), DATE(2019, 12, 31))
Second, I create two measures:
Maxdate each month =
CALCULATE(
MAX('Table'[Date]),
ALLEXCEPT(
'Table',
'Table'[Date].[Year], 'Table'[Date].[Month]
)
)
Mindate each month =
CALCULATE(
MIN('Table'[Date]),
ALLEXCEPT(
'Table',
'Table'[Date].[Year], 'Table'[Date].[Month]
)
)
Third, I count the [service ID] with measure:
Active service =
CALCULATE(
COUNT([Service ID]),
FILTER(
ALLSELECTED(Sheet10),
[Connected Date] <= [Mindate each month]
&&
[Disconnected Date] >= [Maxdate each month]
)
)
The result:
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ToddMate ,
My original data:
First, I create a table with DAX:
Table = CALENDAR(DATE(2019, 5, 1), DATE(2019, 12, 31))
Second, I create two measures:
Maxdate each month =
CALCULATE(
MAX('Table'[Date]),
ALLEXCEPT(
'Table',
'Table'[Date].[Year], 'Table'[Date].[Month]
)
)
Mindate each month =
CALCULATE(
MIN('Table'[Date]),
ALLEXCEPT(
'Table',
'Table'[Date].[Year], 'Table'[Date].[Month]
)
)
Third, I count the [service ID] with measure:
Active service =
CALCULATE(
COUNT([Service ID]),
FILTER(
ALLSELECTED(Sheet10),
[Connected Date] <= [Mindate each month]
&&
[Disconnected Date] >= [Maxdate each month]
)
)
The result:
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |