Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic New Customer count

Hi all,    Small problem to you all.    I have a sales fact table. Call it sales. I have customer dimension data .  Call it customer_dim I have a date table.  Call it date_dim.    Sales fact:...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    You may download my solution PBI file from here.

    Hope this helps.

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous ,

 

As the sample you shared, I think that you want to correct the total, you can change your measure to following:

 

new Customer = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            ADDCOLUMNS (
                DISTINCT ( Sales[Customer] ),
                "Previousdate", MINX ( FILTER ( ALL ( Sales ), Sales[Customer] = [Customer] ), [Sale_date] )
            ),
            "Category", LOOKUPVALUE ( Sales[Product Category], Sales[Sale_date], [Previousdate] )
        ),
        AND (
            YEAR ( [Previousdate] ) >= SELECTEDVALUE ( DateTable[Fiscal Year] ),
            IF (
                ISINSCOPE ( Sales[Product Category] ),
                [Category] = SELECTEDVALUE ( Sales[Product Category] ),
                TRUE ()
            )
        )
    )
)

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, 

     

    I tried it but I am not really sure how this would fit in my problem. I have to do two things:

     

    1) use my calc of new customer

    2) Remove all those how purchased within the fiscal period prior to the sale date. 

    OR

    Change the calc 

     

     

    I added a bit more info on the problem and I can put additional info if needed. 

     

    Thanks


    F

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Anonymous ,

       

      I apologize for posting answer of another post here  what may confuse you, I have updated the answer.

       

      Best regards,

       

      Community Support Team _ DongLi
      If this post helps, then please consider Accept it as the solution to help the other members find it more