Filter/Slicer question - Several collumns

[Kjellke]

Hi everyone,

I have been trying to search the forums for a solution to i problem that i have for a while now, but cannot seem to figure it out.
I have been able to import all my company CRM data into Power BI, and i am now creating reports and a dashboard.

The data is structured in the following way:

Deal ID | Deal Name | Add_Time | Won_Time | Lost_Time | Value

I am trying to make a slider to show monthly data - For example if i select "September", i want it to show the deals that was Added, Won and Lost in Sept (Of course with separate numbers for Added, Won and Lost).

The way i have gone about it now is that i have created a table called Months, Inserted all the months and established a relationship between Months and Closed_Time (A table collumn i have created - IF the deal is won/lost, it inserts that date if it is still open it inserts the Add_Time).


The problem with this is of course if a deal is created in August and won in September, id does not show up for the "Deals Added" in August.

Does anyone have a good suggestion to how i can go about this?

Would be greatly appreciated.

Thank you.

[Kjellke]

@Ashish_Mathur, @PattemManohar Thank you very much for your responses.
I now posted a sample of my data, of course my table includes way more data as deal title, deal owner, customer, segment etc.

I now tried pivoting but it just made a mess of my data.


I have considered importing my data into three tables and connecting them, one table for open deals, one for won deals and one for lost deals. Will this affect my report performance significantly?

Thank you for your response. And i am sorry for my lack of Power BI knowledge.

[v-chuncz-msft]

@Kjellke,

You may check the following example.

https://community.powerbi.com/t5/Desktop/Plotting-two-date-fields-on-one-date-axis/m-p/288525#M12695...

[PattemManohar]

@Kjellke Thanks but it will be great if you can post the sample data in copiable format. Also, post the expected output for the sample data that was providied.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Ashish_Mathur]

Hi,

Select the first, second and last column in the Query Editor and click on "Unpivot other columns".  If the Value1 column has actual date entries, then create a Calendar Table and build a relationship from the Value1 column to the Date column of the Calendar Table.  In the Calendar Table, write this calculated column formula = FORMAT(Calendar[Date],"MMMM").  In the visual, drag the Month column from the Calendar Table.  Now we can write measures.

[Kjellke]

[PattemManohar]

@Kjellke Any sample data will be really helpful to suggest any solution. But I think, You need to create three different measures (Added, Won, Lost). Unpivoting the Added,Won,Lost dates fields and then linking this unpivoted version of table to the Date Dimension...