How to filter over date without reffering to a date table

[Maciek_1000]

Hi,

I've got a dimension table dim_projects. There is a list of projects with names and dates when the project was set. This table is connected with other dimension tables, which are finally connected with some fact tables and master date table.

The case is that I need to show next to i.e. sales sliced by quarters (fact_sale table is connected with the master date table), the number of the projects signed in every quarter. In my opinion, it is impossible to connect the dim_projects table with the master date table because it would create some ambiguity. How can I manage this case to be able to count finally the number of projects signed for each quarter?

[Nathaniel_C]

Hi @Maciek_1000 , Based on your description, your Projects Table is connected to your Sales table, right? If so, just like you create a measure like Total Sales = SUM(SalesFact[Sales]), create a measure on the column that captures the signed project. Total Signed Project = Sum(SalesFact[SignedProject].  Drop the calendar value by quarter, then the two measures in the visual. Hopefully I understood your question.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Maciek_1000]

Hi @Nathaniel_C,

Thank you for your suggestion. The model is more complicated than I described. The Project table is associated with the forecast table and the forecast table is connected to the sales table (so there is another table between them).
Even if there was no table between project and sales table I couldn't count project id from the sales table. That is because there is no correlation between the signing date and the date when is sale.

Information about the signed date is in Project Table. However, sales are in Sales Table (and it is sliced quarterly). It means that I need somehow to connect dates and assign them to the Project table (but I can't connect the Project table to the date table directly). I think I need to make some additional construction but I can't imagine how?

I attach the picture. The orange is Project list table, the blue one is a fact sales table (actually forecasted, but it doesn't matter). 

Maciek

[Nathaniel_C]

Hi @Maciek_1000 , yes you are right.  Your current setup does have ambiguity if you trace the path from project to forecast.  Let's see if @MattAllington is online.  
@MattAllington  who wrote Supercharge Power BI is really good with these type of inquiries!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Maciek_1000 · Accepted solution]

@Nathaniel_Cat this moment I've decided to make a copy of the request of Project Table in Power Query. That has provided me the option to join the new table with dates and to count the number of projects signed in demanded periods. I'm not sure if this is a professional solution but it works fine. Maybe @MattAllington who you mentioned can provide a more elegant solution.

Thank you for your interest in the subject!