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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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

Capture2.JPG

 

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.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

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

Capture2.JPG

 

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.

parry2k
Super User
Super User

@Anonymous Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.