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 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?

  • 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.

5 Replies

  • 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

      Didn't quite work 😕

       

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User

        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.