Creating sales funnel from CRM

[Alex_Yatllen]

Good day!

I am trying to build a report based on crm logs.

The architecture of the base is (pic 1):

Each table contain a data snapshot at the end of the day.

Please help me find a solution how can I set up a sales funnel that will select data (simultaneous from tables “Stages” and “Jobs”) according to the selected day in the slice.

For example I take only one part of full story that is report of Stage (name of stage a kanban board) and Jobs (ticket on kanban board which going through stages).

I try to create report, some one like that, but I can't create adaptive formula for selecting data from an impression by selected date (pic2).  

I consider it important to clarify: stages are not static they are dynamic i.e. when a user adds a new kanban stage, this will appear in the database and by the date selected in the slicer, it is necessary to update the list of funnel stages and jobs.

With best regards.

[v-xicai]

Hi @Alex_Yatllen ,

You can create a calculated table using DAX below, then create relationship with your Calendar table on date field. You can put the 'New Tbale'[StageId] into visuals.

New table= UNION(
SELECTCOLUMNS('Stages', "Date",[Date],"StageId" ,[Id]),
SELECTCOLUMNS('Jobs', "Date",[Date],"StageId" ,[StageId]))

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alex_Yatllen]

Thank you @v-xicai for your answer.

Here is planting the PBIX file. PBIX file with the sample

Please help my to create the report with functional abbility you can read on the picture (and the same picture you may view in PBIX file).

If you need more information or more rows in the data set - write me and I will update the data set.