dynamic dates with a direct query

[Seiryth]

Hi All, 

Just a few questions. I'm relatively new to powerbi so bare with me 🙂 

1) I've got two tables from a mssql database that im direct querying against - sales and product. both have an "account manager" column. when i create visual displays, i have to click each account manager individually rather than powerbi recognise that "john smith" in both columns is the one and the same john smith.. how do i go about telling powerbi they are the same value essentially? 

2) similar to the above, how can i get my powerbi reports to dynamically show sales for this month, next month and the next when direct querying.. or do i need to change to import mode? 

I'm currently using powerbi desktop, if that helps. 

[v-ljerr-msft]

@Seiryth

1) I've got two tables from a mssql database that im direct querying against - sales and product. both have an "account manager" column. when i create visual displays, i have to click each account manager individually rather than powerbi recognise that "john smith" in both columns is the one and the same john smith.. how do i go about telling powerbi they are the same value essentially? 

In this scenario, you can use the "account manager" column to create a relationship between sales and product table in Relationships View, then the two tables will be related by the "account manager" column. For more details about how to create and manage relationships in Power BI Desktop, please refer to the articles below.

How to Manage Your Data Relationships

Create and manage relationships in Power BI Desktop

2) similar to the above, how can i get my powerbi reports to dynamically show sales for this month, next month and the next when direct querying.. or do i need to change to import mode? 

In Direct Query Model, we can still create Measures and Calculate Columns using DAX in Modeling tab. So you can create a measure to calculate the total sales first, then show it with Slicers in the report like below to show sales for a specific month in Direct Query Model.

Regards

[Seiryth]

Hi, 

So two small issues I've got post looking at your reply, post creation of that relationship.

I've created two pie graphs to show total sales divided by BDM for product and services. When the relationship was applied, it seems to have maintained the date filter i had on one table but is not applying the existing date filter unique to the other table. this has resulted in the second pie graph showing all services sales total in the database, rather than a date range. any idea why a relationship would cause my filter to stop working?

in terms of the dates, rather than using a slicer specific to the month, i was hoping to utilise logic so it realises the current month is "october" and demonstrate the sales for october without a user having to manually apply a monthly filter.  

[GilbertQ · Accepted solution]

I would suggest that you either bring in your date table, or create a date table in your Power BI Model. In doing so if both tables are related to the date table, you can then filter using the date, which will then filter both data sets.

Along with this, in your date table you could create a calculated column or even in your underlying date table source, in this column you can create the logic which will tell you which month is the current month. So for example in your date table, when the dates are for October 2016, your calculated column will return a value of "This Month", and for all other dates it will return "Other Months"

Then in your Power BI Model, you can add the filter to either the visual level filter, page level filter or report level filter and select "This Month" from your calculated column. In doing so everytime you refresh your data it will then update and reflect the current month.