Forum Discussion

AlAlawiAlawi's avatar
AlAlawiAlawi
Icon for Helper I rankHelper I
8 years ago
Solved

Multiple Dates for funnel-ish!

Hello Everyone;   I am frustrated with this and waiting for super geek to hit me on the head. The screen shot says it all, first part is te sample data, Then usually what most look at are if the ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi AlAlawiAlawi,

     

    Data1 has been auto generated by using Power Query.  So the end user will maintain data the way you uploaded it.  The internal process will create Data1 and get your desired result.  The end result is much simpler DAX Formulas as compared to my first solution.

     

AlAlawiAlawi's avatar
AlAlawiAlawi
Icon for Helper I rankHelper I
8 years ago

Ideally, For a KPI; All activities count.

 

So if a executive had been active and had recieved an Inbound for XYZ in January but an order in Jun, then both activies counts.

It just needs to be segmented as such.

I am using a Distinct Count anyway so the YTD Should Flush the Duplicates of Customers by ID.

 

Thanks for your reply

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi AlAlawiAlawi,

 

If you are doing a YTD count, then the number of customers for each of those months should be 2.  Isn't that correct?

  • AlAlawiAlawi's avatar
    AlAlawiAlawi
    Icon for Helper I rankHelper I
    8 years ago

    Absolutely true, Even if they had 3 activities registered (Inbound/Order/Invoice) .. the distinct count shouldn't be affected and should show only 2 clients in 2017.

    How ever it should show 2 in Jan, 1 in Jun, 1 in July, 1 in Nov, 1 in Dec as in the initial example.

     

    Edit: I just noticed my initial example was a sum of the total months and showing 5 ...

    You are however correct and it should have been 2 (Good Eye) 

      • AlAlawiAlawi's avatar
        AlAlawiAlawi
        Icon for Helper I rankHelper I
        8 years ago

        Hi Ashish_Mathur

         

        Thank you for that spread sheet, it magic to me. Break it to me Gently =D ...

         

        There is no Month Column in the calendar table ... and it seems that we are linking the column to itself.

         

        The most interesting part then is all that formula.... What is ABCD.

         

        Number of clients = if(HASONEVALUE('calendar'[Month]),

        CALCULATE(DISTINCTCOUNT(Data[Client ID]),USERELATIONSHIP(Data[Inboud date],'calendar'[Date])) +CALCULATE(DISTINCTCOUNT(Data[Client ID]),USERELATIONSHIP(Data[Order Date],'calendar'[Date]))

        +DISTINCTCOUNT(Data[Client ID]),MAXX(SUMMARIZE('calendar','calendar'[Month],"ABCD",

        CALCULATE(DISTINCTCOUNT(Data[Client ID]),USERELATIONSHIP(Data[Inboud date],'calendar'[Date])) +CALCULATE(DISTINCTCOUNT(Data[Client ID]),USERELATIONSHIP(Data[Order Date],'calendar'[Date]))

        +DISTINCTCOUNT(Data[Client ID])),[ABCD]))

         

         

        Ashish THANK YOU for your time ... that was very generous from you.

         

        I'll work on this more now and come back, for more as I still cant get my head around the logic. I could figure out the code reading it more than once but not sure about the relationship ....

         

        The additional table that I now need to create would be the month order table to try it out.