Forum Discussion
Automatically filter month based on real date
Hi guys, I'm currently an finance officer and on of my monthly task is to create a PBI dashboard to present data of company. I want to automate my report so that once I have new data, I just have to refresh and PBI will do everything. Currently, I have a trouble with my task.
My data contain transactions of all month, spread year to year. For the purpose of the report, I only extract two most recent months for calculation and visualization. Data will look like this ( assume name of this table below is Data):
| Date | Transactions |
| 17-10-23 | A |
| 18-10-23 | B |
| 19-10-23 | C |
| 16-11-23 | D |
| 15-11-23 | E |
| 19-11-23 | F |
| 24-12-23 | G |
| 12-12-23 | H |
| 23-12-23 | I |
For example, in December, I only take transactions of November and October for reporting. I create a new calculated column name Month, in which is the month number of column date. Then I create a new table with the formula:
Data_report = Filter(Data, Data[Month]=Month(today())||Data[Month] = Month(today())-1)
With this DAX, each time I have to do monthly report, data will be selected for the most 2 recent months.
Usually, this DAX runs well. However, since it is now Jannuary, this dax no longer runs. I realized the problem is my Dax now calculate results will be 0 and -1, not 11 or 12 as I expected.
Thus, anyone can help me to fix my DAX or create a new one to automate the data processing.
Sorry English is not my first language, so my post is difficult to understand, please leave me a message.
Many thanks
Regards,
You should be able to do it just using the dates without adding the month to the table like this.
Data_report = VAR _Start = EOMONTH(TODAY(),-2)+1 VAR _End = EOMONTH(TODAY(),0) RETURN FILTER ( Data, Data[Date] >= _Start && Data[Date] <= _End )Hi Nerdywantocode ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
1.Create a new calculated column in `Data` table to determine the year and month combination for each transaction date.
YearMonth = YEAR(Data[Date]) * 100 + MONTH(Data[Date])2.Create a measure.
Two months ago = IF( MONTH(TODAY()) = 1 || 2, (YEAR(TODAY()) - 1) * 100 + 11, YEAR(TODAY()) * 100 + MONTH(TODAY()) - 2 )3.Create another measure to calculate the year and last month combination.
LastMonth = IF( MONTH(TODAY()) = 1 || 2, (YEAR(TODAY()) - 1) * 100 + 12, YEAR(TODAY()) * 100 + MONTH(TODAY()) - 1 )4.Create a calculate table to show the final output
Table = CALCULATETABLE(Data, FILTER( Data, Data[YearMonth] = [Two months ago] || Data[YearMonth] = [LastMonth]) )5.Final output
Best regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
You should be able to do it just using the dates without adding the month to the table like this.
Data_report = VAR _Start = EOMONTH(TODAY(),-2)+1 VAR _End = EOMONTH(TODAY(),0) RETURN FILTER ( Data, Data[Date] >= _Start && Data[Date] <= _End )Thank you. I will try this later with my data in the company tmr
Hi Nerdywantocode ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
1.Create a new calculated column in `Data` table to determine the year and month combination for each transaction date.
YearMonth = YEAR(Data[Date]) * 100 + MONTH(Data[Date])2.Create a measure.
Two months ago = IF( MONTH(TODAY()) = 1 || 2, (YEAR(TODAY()) - 1) * 100 + 11, YEAR(TODAY()) * 100 + MONTH(TODAY()) - 2 )3.Create another measure to calculate the year and last month combination.
LastMonth = IF( MONTH(TODAY()) = 1 || 2, (YEAR(TODAY()) - 1) * 100 + 12, YEAR(TODAY()) * 100 + MONTH(TODAY()) - 1 )4.Create a calculate table to show the final output
Table = CALCULATETABLE(Data, FILTER( Data, Data[YearMonth] = [Two months ago] || Data[YearMonth] = [LastMonth]) )5.Final output
Best regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
