How to show new datas in my column between one date and the dates before it

[Bastienlin_]

Hello,

I would like to display a table where I show the new datas in a column between a selected date from a slicer, and all the dates before. Here is an example of what I need to show:

Fact_table:

City		Date		DateQuarter
Paris		31/03/2019		2019-Q1
London		31/03/2019		2019-Q1
Paris		30/06/2019		2019-Q2
London		30/06/2019		2019-Q2
Madrid		30/06/2019		2019-Q2
Paris		30/09/2019		2019-Q3
London		30/09/2019		2019-Q3
Tokyo		30/09/2019		2019-Q3
Paris		31/12/2019		2019-Q4
London		31/12/2019		2019-Q4
Barcelona		31/12/2019		2019-Q4

1. With this data, If I select in my date slicer 2019-Q4, I want to show new datas for City compared to all the dates before (new cities from 2019-Q4, compared to all cities from all dates before). Here it would display:

Barcelona

because I never had the value "Barcelona" in my previous dates before 2019-Q4

2. If I select in my slicer 2019-Q3, it would display

Tokyo

because I never had the value "Tokyo" in my previous date before 2019-Q3

Thank you !

[Anonymous · Accepted solution]

Hi @Bastienlin_ ,

Please new a measure like:

Measure = 
var _t1= SUMMARIZE(FILTER(ALL('Table'),YEAR([Date])*10 + QUARTER([Date])<MAX('For Slicer'[Number])),'Table'[City])
var _t2= SUMMARIZE(FILTER(ALL('Table'),[DateQuarter]=MAX('For Slicer'[DateQuarter])),'Table'[City])
return 
EXCEPT(_t2,_t1)

Result:

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[amitchandak]

@Bastienlin_ , seem the code of new vs retained customer will work, you need use qtr, datesqtd in place of datesmtd 

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention:https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Dynamic Segmentation, New/Lost/Retain Customer Count:
Learn Power BI Advance - Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY