Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have 3 columns
and I want to get active customers for current month where status = "won"
and active customers for previous month where status = "won"
for current month im using
expected solution:
eg: if today is 12 March 2021:
I want active users of the current month March 2021: 5364 users
and previous month i.e Feb 2021 :
active users 3265 users.
customer | date | status |
xyz | 03-01-2018 | won |
abc | 03-01-2018 | lost |
efd | 03-01-2018 | won |
ghy | 03-01-2018 | lost |
tgh | 05-02-2018 | won |
fht | 01-01-2019 | won |
Solved! Go to Solution.
Hi @Anonymous ,
You can try these measures:
CurrentMonthCount =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Customer] ),
FILTER (
'Table',
YEAR ( 'Table'[Date] ) = YEAR ( SELECTEDVALUE ( 'Date'[Date] ) )
&& MONTH ( 'Table'[Date] ) = MONTH ( SELECTEDVALUE ( 'Date'[Date] ) )
&& 'Table'[Status] = "won"
)
)
LastMonthCount =
VAR lastmonth =
EOMONTH ( SELECTEDVALUE ( 'Date'[Date] ), -2 ) + 1
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[Customer] ),
FILTER (
'Table',
YEAR ( 'Table'[Date] ) = YEAR ( lastmonth )
&& MONTH ( 'Table'[Date] ) = MONTH ( lastmonth )
&& 'Table'[Status] = "won"
)
)
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
the data is huge I just made a small snippet of it for understanding.
I have data from Jan 2018 to August 2021.
I get accurate active customers for the current month but I don't know how to get active users for the previous month.
my measure for the previous month just doesn't work.
@Anonymous what do you mean by active customers?
Do you need cumulative distinct count of wins per yr-month
Measure2 =
CALCULATE (
DISTINCTCOUNT ( 'Table'[customer] ),
FILTER ( ALL ( 'Table'[ date] ), 'Table'[ date] <= MAX ( 'Table'[ date] ) ),
'Table'[ status] IN { "won" }
)
yes, I want the cumulative distinct count of active users for the current month and the previous month.
Then try the measure i gave
how do I use it for finding previous month's active users
@Anonymous Hi, could you share mroe data? the data you've shared above does not have Feb & Apr data hence it is natural to show no value. When your date filter context is Jan, there is no data for last year Dec, and when date filter is March, there is no data for Feb, and so on.
I have data from Jan 2018 to August 2021, I have just shown the above data as a sample snippet.
need help with previous months active users
Hi @Anonymous ,
You can try these measures:
CurrentMonthCount =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Customer] ),
FILTER (
'Table',
YEAR ( 'Table'[Date] ) = YEAR ( SELECTEDVALUE ( 'Date'[Date] ) )
&& MONTH ( 'Table'[Date] ) = MONTH ( SELECTEDVALUE ( 'Date'[Date] ) )
&& 'Table'[Status] = "won"
)
)
LastMonthCount =
VAR lastmonth =
EOMONTH ( SELECTEDVALUE ( 'Date'[Date] ), -2 ) + 1
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[Customer] ),
FILTER (
'Table',
YEAR ( 'Table'[Date] ) = YEAR ( lastmonth )
&& MONTH ( 'Table'[Date] ) = MONTH ( lastmonth )
&& 'Table'[Status] = "won"
)
)
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
27 | |
23 | |
22 | |
22 |