Forum Discussion
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.
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)
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.
Hi AlAlawiAlawi,
You are welcome. In the calendar table, there is a month column which i have calculated using the FORMAT() function. The relationship shown in your screenshot below is needed because i need to bring over the Order column from the Month_order table to the Calendar table. This is required to use the "Sort by column" feature in the Calendar Table so that the months in the visual appear in proper order.
ABCD is the heading of a virtual column that i have created in the SUMMARIZE() function.
If my reply helped, please mark it as Answer.
I think the query summary is missing something,
Please check the result after adding the last line from the table below.
Client ID Inboud date Order Date Amount Invoice Date
1212 01-12-17 15-12-17 100 31-12-17
The clients gets counted as 3 in December if you add the above line.