Forum Discussion

rhl94's avatar
rhl94
Icon for Advocate III rankAdvocate III
6 years ago
Solved

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 ...
Need Help
  • dax's avatar
    dax
    6 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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

  • rhl94's avatar
    rhl94
    Icon for Advocate III rankAdvocate III
    6 years ago

    Didn't quite work 😕

     

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      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.

      • rhl94's avatar
        rhl94
        Icon for Advocate III rankAdvocate III
        6 years ago

        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.