Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Table CC
Date | AN |
1/1/2018 | 1 |
1/1/2018 | 2 |
1/1/2018 | 2 |
2/1/2018 | 3 |
2/1/2018 | 4 |
2/1/2018 | 1 |
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
Solved! Go to Solution.
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.
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.
@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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |