Forum Discussion
Helper IIHow to auto show current month data when connected to server ?
HI,
I have coneected my comapny server to get data I need for reporting.
What I want is to automate showing only current month data without filtering.
For example attached photo shows all pervious data the server has and I want to show only December and then only January when it comes and so on.
appricate your support. thanks
naifAhmedB If you want DAX, then it will only apply to that measure/column/table that you have written. That means you will still be importing ALL the historical data even though you aren't using it, which is really NOT efficient.
If you want DAX relative date filters, you can try using
MONTH(Today()) = MONTH(table[Date]) && YEAR(TODAY()) = YEAR(Table[Date])
in the filter expression of your DAX measure/calculation.
However, as I stated this won't filter the entire report, so if you want more support on that please provide more details on what measure/calculation/result you're looking for.
Also see if this helps give some insight on how Power BI refresh works (and feel free to comment with any questions as I know it doesn't tell everything so there are some gaps that can be filled in). Power BI Order of Operations
3 Replies
- AllisonKennedy
Community Champion
naifAhmedB There are a few options;
Option A: If you are connecting to a SQL database, you can use SQL to filter before connecting to the company server.
Option B: If you are importing the data, you can use Power Query to filter the date column. Click Home tab > Transform Data > select the query then click the down filter arrow at the top of the date column and choose 'relative date filters' > Custom. Select the option for 'this month' or 'previous month', depending on what result you want. Then click Close and Apply. Now, everytime you open/refresh the report it will show only the current/previous month data (depending on your selection).
Option C: (Not recommended if you only want the current/last month data in the report) You can use a 'filter on all pages'.
naifAhmedBThank you for your reply.
I can't do option 1 at the moment.
option 2 is really good but I prefer If I could write Dax that will show current month data while keeping previous months in the query. would dax be better? and how can I write it for valueIn?
Option3 i won't use it.
Thank you.
- AllisonKennedy
naifAhmedB If you want DAX, then it will only apply to that measure/column/table that you have written. That means you will still be importing ALL the historical data even though you aren't using it, which is really NOT efficient.
If you want DAX relative date filters, you can try using
MONTH(Today()) = MONTH(table[Date]) && YEAR(TODAY()) = YEAR(Table[Date])
in the filter expression of your DAX measure/calculation.
However, as I stated this won't filter the entire report, so if you want more support on that please provide more details on what measure/calculation/result you're looking for.
Also see if this helps give some insight on how Power BI refresh works (and feel free to comment with any questions as I know it doesn't tell everything so there are some gaps that can be filled in). Power BI Order of Operations