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
rhl94
Advocate III
Advocate III

New Customers YTD subscription

Hi

I'm looking to create a YTD new customers. As the customers get renewed, I have to find the minimum date in order to figure out when they started
The whole data model is pure star schema.
In table fact_PP I have the PP_ID and in dim_calendar I can filter by the dates.

I have created the following measure, which returns YTD, but when creating a table, it gets restricted by the dates, which means it basically show the number of new customers in the given month instead of YTD

new customers YTD  = 
VAR tbl = FILTER( SUMMARIZE (
        'fact_PP',
        'fact_PP'[ID],
        "OrginalDate", MIN('Calendar'[Date]))
    , [OrginalDate] > DATE ( YEAR(TODAY())-1, 12, 31 ))
RETURN
COUNTROWS(tbl1)

Which gives the following results. The 4986 is the actual 2020 YTD number, but as you can see, the measure gets restricted by the Year and Month columns. I can't apply an ALL filter since it's based on a VAR table. Any ideas?

image.png

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

Hi @rhl94 , 

I am not sure your expected output, you could refer to my sample for details. If this is not what you want, please correct me.

Best Regards,
Zoe Zhi

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

5 REPLIES 5
amitchandak
Super User
Super User

@rhl94 ,

Try

Meausre =
var _min =calculate(MIN('Calendar'[Date]),datesytd('Calendar'[Date]))
return
countx(summarize('fact_PP','fact_PP'[ID], "OrginalDate",calculate(MIN('Calendar'[Date]),filter(all(date),'Calendar'[Date]=_min ))),[ID])

Didn't quite work 😕

 

 

image.png

@rhl94 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

if you need more help make me @

Appreciate your Kudos.

Ok, So I have tried to recreate a pbix with the same structure.

 

https://www.dropbox.com/s/ck6ufzfbin14x2x/sampledataYTDNewBusiness.pbix?dl=0

 

There might be overlap in the start-end date periods, but I created the numbers using randbetween, so just ignore that.

 

So for example, ID 229 should not be a new customer, since there is a renewaldate from before 2020.

 

Likewise ID 496 should be a new customer, since its first appearance is in 2020.

 

After this measure, I also need to define the lost customers, but I thought it would be quite similar - not sure though. A customer in 2019, that has no renewal in 2020 is not necessarily a lost customer, but rather if the ID doesnt have an end date in the future, it should be a lost customer.

 

I've tried to follow the dax pattern -> https://www.daxpatterns.com/new-and-returning-customers/ but without luck.

 

@amitchanda

 

edit: the real model is based off a ssas model, so a power query solution is not possible (e.g. Group by min date). I could add a calculated column in the ssas model, but would prefer not to. 

dax
Community Support
Community Support

Hi @rhl94 , 

I am not sure your expected output, you could refer to my sample for details. If this is not what you want, please correct me.

Best Regards,
Zoe Zhi

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

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.