Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Finding Customers not in Previous Month

Table CC

DateAN
1/1/20181
1/1/20182
1/1/20182
2/1/20183
2/1/20184
2/1/20181

 

 

I am trying to Calculate Distinct count of AccountNumbers that are not in Previous Month given a particular month on a line chart

From the example Data 

if I select 2/1/2018 then ANS is 2 since account number 3, 4 are not in previous month 1/1/2018

 

This is not working

CALCULATE(DISTINCTCOUNT(CC[AN]), FILTER(CC, CC[Date] = CC[Date] && NOT CONTAINS(FILTER(CC, CC[Date] = PREVIOUSMONTH(CC[Date])), CC[AN], CC[AN])))

 

This mesure is working but I have to provide it with dates
CALCULATE(DISTINCTCOUNT(CC[AN]), FILTER(CC, CC[Date] = DATE(2018,2,1) && NOT CONTAINS(FILTER(CC, CC[Date] = DATE(2018,1,1)), CC[AN], CC[AN])))

 

Thank You for the help.

 

Made the question clear parry2k  Thank you for the suggestion

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Create a calendra table, create a relationship between this calendar table and your table

    calendar = ADDCOLUMNS( CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

    Create measures in your table

    Measure =
VAR currentmonth =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        COUNT ( 'Table'[AN] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[AN] ),
            DATEDIFF ( currentmonth, 'Table'[Date], MONTH ) = -1
        )
    )

Measure 2 = IF(MAX('Table'[AN])<>BLANK(),CALCULATE(DISTINCTCOUNT('Table'[AN]),
                FILTER(ALLEXCEPT('calendar','calendar'[year],'calendar'[month]),[Measure]=BLANK())))

     

    Best Regards

    Maggie Community Support Team _ Maggie Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Create a calendra table, create a relationship between this calendar table and your table

    calendar = ADDCOLUMNS( CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

    Create measures in your table

    Measure =
VAR currentmonth =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        COUNT ( 'Table'[AN] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[AN] ),
            DATEDIFF ( currentmonth, 'Table'[Date], MONTH ) = -1
        )
    )

Measure 2 = IF(MAX('Table'[AN])<>BLANK(),CALCULATE(DISTINCTCOUNT('Table'[AN]),
                FILTER(ALLEXCEPT('calendar','calendar'[year],'calendar'[month]),[Measure]=BLANK())))

     

    Best Regards

    Maggie Community Support Team _ Maggie Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.