List of Customer having No sale in Current Month But the sale is done previous Month

[mh2587]

Hi 

I hope you people are doing well 

I am stuck in simple scenario i.e the list of customer having no sale in Current Month but the sale is done last month 

e.g In Jan Month the customer have sale but in Feb month there is no sale like I want those customer which is Highlighted in Screenshot 

[v-yiruan-msft · Accepted solution]

Hi @mh2587 ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a date dimension table

2. Create a measure as below to judge whether the customer hasn't current month sales but has the previous month sales 

Flag = 
VAR _selmonth =
    SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
var _selcustomer=
    SELECTEDVALUE ( 'Table'[Customer Name])
VAR _cursales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', MONTH ( 'Table'[Date] ) = _selmonth )
    )
VAR _presales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Customer Name] =  _selcustomer
                && MONTH ( 'Table'[Date] ) = _selmonth - 1
        )
    )
VAR _minmonth =
    MONTH ( CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) ) )
VAR _maxmonth =
    MONTH ( CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) ) )
RETURN
    IF (
        _selmonth < _minmonth
            || _selmonth > _maxmonth,
        BLANK (),
        IF ( ISBLANK ( _cursales ) && NOT ( ISBLANK ( _presales ) ), 1, 0 )
    )

3. Create the measures to get the sum of sales base on month name and customer name

Sum of sales =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        MONTH ( 'Table'[Date] ) = SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
    )
)

Measure = SUMX(VALUES('Date'[Date].[Month]),[Sum of sales])

4. Create a matrix visual as below screenshot and make conditional formatting for Measure

Best Regards

[mh2587]

Hi Can you help me in this @amitchandak 

[Greg_Deckler]

@mh2587 Without knowing your data specificaly, you could do something like:

Measure =
  VAR __Today = TODAY()
  VAR __Year = YEAR(__Today)
  VAR __Month = MONTH(__Today)
  VAR __LastMonth = EOMONTH(__Today,-1)
  VAR __LMYear = YEAR(__LastMonth)
  VAR __LMMonth = MONTH(__LastMonth)
  VAR __CurrentCustomers = SELECTCOLUMNS(FILTER('Table',[Month]=__Month && [Year]=__Year),"Customer Name",[Customer Name])
  VAR __LastMonthCustomers = SELECTCOLUMNS(FILTER('Table',[Month]=__LMMonth && [Year]=__LMYear),"Customer Name",[Customer Name])
RETURN
  CONCATENATEX(EXCEPT(__LastMonthCustomers,__CurrentCustomers),[Customer Name],", ")

[mh2587]

Thank you for the response but its not working