Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

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



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.


Ashish Mathur

View solution in original post

Super User
Super User

Hi @AlAlawiAlawi,


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

Ashish Mathur

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?

Ashish Mathur

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) 

Hi @AlAlawiAlawi,


You may download my solution from here.


Hope this helps.

Ashish Mathur

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]))




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.

Ashish Mathur

Hi @Ashish_Mathur

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.

Hi @AlAlawiAlawi,


Refer to the result1 worksheets.  The link remains the same.

Ashish Mathur

Hi @Ashish_Mathur;


The data structure change wouldn't be possible, as in the current excel suggestion,

Although it would be much easier to get that result and much less formula's.



Why not?  The change is structure is happening at the backend.

Ashish Mathur

Pardon my limited understanding,


1. Do you mean that the Data1 is automatically created using some formula ?


2. Create a New Data Source / Table with the suggested data structure ?


With changing the data structure and adding a new table,

I would require to link it to the current table that has over 60 Columns worth of Data and Growing.


This would ultimately affect the processing speed and load time for Maps on the BI +++ and never ming the refesh time.

Mind you the current data source is a .csv


This is why I had hit the wall on this issue and need a formula to grab those results without changing the structure.


I had been researching google for a couple of days now however this is not your typical {index/match how to video} .. haha

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.


Ashish Mathur

Good Day @Ashish_Mathur




Thank You very much for the time spent on this topic and the effort included ...

You are a blessing for this forum / community and I can't express how your hint had broadend my view with unpivoting tables ...


This took me a while to go around and re-structure new measures to accomodate to pre-existing dashboards which was a much bigger pain. This re-structure of new tables columns measures truely allowed a more dynamic approach to how I could segment things which weren't possible due to my limited sight of the unpivot option.


You havent answered only my question but also took me to a new learning curve that will help me moving forward.


Thank You




Thank you for your kind words.  Glad to help.

Ashish Mathur

You need a calendar table.


Then you need 2 relationships, one for each of your date columns.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

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

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors