Forum Discussion
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 ! :robotwink:
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
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/
Thank you for the response; Ill check this solution and reply back today if possible. Appreciate your reply dear.
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.
Hi AlAlawiAlawi,
How have you calculated the Count of Client ID. Why should that show 1 for all months?
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
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?
