Forum Discussion
dynamic dates with a direct query
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.
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.
Super UserI 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.