cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AlAlawiAlawi Regular Visitor
Regular Visitor

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 Sales Figures match the month so predominantly invoice date is the filter

The last bis is what the data should look like.

 

How would super geek reach that required result ! Robot wink

Problem.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Multiple Dates for funnel-ish!

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.

 

18 REPLIES 18
Super User
Super User

Re: Multiple Dates for funnel-ish!

You need a calendar table. https://exceleratorbi.com.au/power-pivot-calendar-tables/

 

Then you need 2 relationships, one for each of your date columns. https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: Multiple Dates for funnel-ish!

Hi @AlAlawiAlawi,

 

How have you calculated the Count of Client ID.  Why should that show 1 for all months?

AlAlawiAlawi Regular Visitor
Regular Visitor

Re: Multiple Dates for funnel-ish!

Thank you for the response; Ill check this solution and reply back today if possible. Appreciate your reply dear.

AlAlawiAlawi Regular Visitor
Regular Visitor

Re: Multiple Dates for funnel-ish!

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

Super User
Super User

Re: Multiple Dates for funnel-ish!

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 Regular Visitor
Regular Visitor

Re: Multiple Dates for funnel-ish!

Thank you very much for the Eye Opener (I suppose I would need to redo my Dashboards now since I would change most of the Dates field previously used to the New Table But anyway ... Thats not my problem now.

 

I had created a New Date Table and a Date Column to create all the necessary dates that would be needed ever and had reached to this as my current problem;

New Leads = CALCULATE(Leads[Leads] ,

USERELATIONSHIP('Date'[Date] , Leads[Inbound Date] && Leads[Order Date]  && Leads[Invoice Date] ))

 

I need the Value to be calculated based on multiple relationships of Date Columns as shown above .. 

Leads[Leads] = Distinct Count of Client ID

 

In Other Words if the Client had a call in Jan a Order in June and Invoice in November; he would show on all the 3 months.

FYI also ... All the 3 Date Columns are in the same table as illustrated in the example initially.

AlAlawiAlawi Regular Visitor
Regular Visitor

Re: Multiple Dates for funnel-ish!

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) 

Super User
Super User

Re: Multiple Dates for funnel-ish!

Hi @AlAlawiAlawi,

 

You may download my solution from here.

 

Hope this helps.

AlAlawiAlawi Regular Visitor
Regular Visitor

Re: Multiple Dates for funnel-ish!

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.

calendar month link.jpg

 

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 210 members 1,896 guests
Please welcome our newest community members: