cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Active Customers in Financial Year

Hi

 

I have been wrestling with this issue all weekend and it should be pretty straightforward however I must be missing something.

 

I am building a report to show total customers in any financial year & I have already built these first measures successfully:

 

CUST 2016-17 =
            VAR CustomerCreation = FILTER(CUSTTABLE,CUSTTABLE[CREATEDDATETIME] <= DATE(2017,03,31))
            RETURN
            COUNTROWS(CustomerCreation)

 

This brings me back to total number of customers.

 

I then need to establish over that particular financial year how many of these customers actually purchased.

I already have a last sales date measure:

 

Last Sales Date = LASTDATE(SALESLINE[DELIVERY DATE])

 

So my logic is I use this within a date filter to count the respective customers, so I have written:

 

Active Customers =
   VAR TotalActive = CALCULATE(DISTINCTCOUNT(SALESLINE[CUSTACCOUNT]),FILTER(SALESLINE,SALESLINE[DEPOT] = "UK"))
    RETURN
    IF([Last Sales Date],DATESBETWEEN('DATE'[Date],01/04/2016,31/03/2017),TotalActive,BLANK())

 

I must be missing something and I cannot see it as the variable returns all unique customers in the UK, then the IF statement should apply the dates filter and return the DISCTINCTCOUNT stored in the variable or BLANK.

 

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper II
Helper II

Re: Active Customers in Financial Year

Hi

 

I think I have solved it:

 

Active Customers =
   VAR FinalActCust = CALCULATE(DISTINCTCOUNT(SALESLINE[CUSTACCOUNT]), FILTER(SALESLINE,SALESLINE[DELIVERY DATE] >=   DATE(2016,04,01) && SALESLINE[DELIVERY DATE] <= DATE(2017,03,31) && SALESLINE[DEPOT] = "UK"))
    RETURN
    FinalActCust

 

Thanks for the help anyway

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Re: Active Customers in Financial Year

The Active Customers is not proper DAX syntax, can you double check? 

I see 2 issues in the last conditional

IF([Last Sales Date],DATESBETWEEN('DATE'[Date],01/04/2016,31/03/2017),TotalActive,BLANK())

1) [Last Sales Date] is not TRUE/FALSE statement, so IF will always return TRUE (except for 1 date from 1900s)
2) your IF has 4 arguments instead of 3 - Logical test, TRUE  value, FALSE value, the BLANK in the end is not proper

 

The variable TotalActive returns the number of unique customers, not the list of unique customers

what do you want to achieve in the end? do you want to see how many of the [CUST 2016-17] customer bought something during that financial year?

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Highlighted
Helper II
Helper II

Re: Active Customers in Financial Year

Hi Thanks for the reply

 

Yes in essence I do want to find out how many customers bought in any particular financial year.

 

Appreciate the help.

Highlighted
Helper II
Helper II

Re: Active Customers in Financial Year

Hi

 

I think I have solved it:

 

Active Customers =
   VAR FinalActCust = CALCULATE(DISTINCTCOUNT(SALESLINE[CUSTACCOUNT]), FILTER(SALESLINE,SALESLINE[DELIVERY DATE] >=   DATE(2016,04,01) && SALESLINE[DELIVERY DATE] <= DATE(2017,03,31) && SALESLINE[DEPOT] = "UK"))
    RETURN
    FinalActCust

 

Thanks for the help anyway

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors