Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
raghushettyyy
Frequent Visitor

calculate active customers previous month and current month

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 

ActiveMembers = CALCULATE(DISTINCTCOUNT(Table[Customer]),PARALLELPERIOD(table[Date],0,MONTH),FILTER(
        'Table','table'[Status] ="won" ))
 
and it's working fine.
 
but i have getting no value for previous month and I'm using:
ActiveMemberPreviousMonth = CALCULATE(DISTINCTCOUNT('table'[Customer]),PREVIOUSMONTH('table'[Date]), FILTER('table','table'[Status]="Won"))
 
Problem statement: I want help to get current active member and previous month active member where status = "Won"
 

 

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.

 
 
my example data for the table is shown below

 

customer date status
xyz03-01-2018 won
abc03-01-2018 lost
efd03-01-2018 won
ghy03-01-2018 lost
tgh05-02-2018 won
fht01-01-2019 won
1 ACCEPTED SOLUTION

Hi @raghushettyyy ,

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"
        )
    )

vyingjl_0-1634018365097.png

 

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.

 

View solution in original post

9 REPLIES 9
raghushettyyy
Frequent Visitor

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.

 

smpa01
Super User
Super User

@raghushettyyy  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" }
)

 

smpa01_0-1633640370668.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

yes, I want the cumulative distinct count of active users for the current month and the previous month.

 

Then try the measure i gave

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

how do I use it for finding previous month's active users

colacan
Resolver II
Resolver II

@raghushettyyy  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 @raghushettyyy ,

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"
        )
    )

vyingjl_0-1634018365097.png

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors